Make 'select count' include even 0s...

[ChrisFlynn]

MySQL - 5.0.45

 

select date_format(Date, '%d/%m/%Y') as 'Date', count(*) as 'Sales'
from Transaction
where productID='5'
group by date_format(Date, '%d/%m/%Y')
order by date_format(Date, '%d/%m/%Y')

 

Result:

 

Date Sales 
12/02/2008 1 
14/02/2008 3 

 

 

I want to have every day displayed; from the first one that exists (min Date) up to today. E.g.

 

Date Sales 
12/02/2008 1
13/02/2008 0
14/02/2008 3
15/02/2008 0

 

 

I'm guessing I need some kind of join, but I can't figure it out - what's the most elegant way of doing it?

[Daniel0]

How does your table look?

[ChrisFlynn]

How does your table look?

 

Basically (I've cut the crap):

 

CREATE TABLE `transaction` (
  `Email` varchar(255) NOT NULL,
  `Date` datetime NOT NULL,
  `TransactionID` varchar(255) NOT NULL,
  `ProductID` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`TransactionID`)
)

 

Each row represents a successful transaction.

[Barand]

Two approaches

 

1 ) another table with a row for each date and use "FROM dates LEFT JOIN transaction

 

2 ) create an array with keys for each date and put the query results into this array.

[ChrisFlynn]

Two approaches

 

1 ) another table with a row for each date and use "FROM dates LEFT JOIN transaction

 

Thanks for the answer - this definitely makes sense!

 

What's the best way to create this 'dates' reference table, and fill it up? I'd normally use Excel's Autocomplete, but I can't get it to format right... Is there a nice SQL way of filling things up? ['01/01/2007'..'31/12/2010']

 

Chris

[Barand]

<?php
mysql_connect('localhost');
mysql_select_db ('test3');

mysql_query ("CREATE TEMPORARY TABLE tdates ( adate DATE NOT NULL PRIMARY KEY)");

$mindate = '2008-02-01';

$sql = "INSERT INTO tdates VALUES ";

$t = strtotime($mindate);
$dates = array();

while ($t <= time())
{
    $d = date('Y-m-d', $t);
    $dates[] = "('$d')";
    $t = strtotime ('+1 days', $t);
}

$sql .= join (',', $dates);

mysql_query ($sql);

/**
* check it worked
*/
$res = mysql_query("SELECT adate FROM tdates");
while ($r = mysql_fetch_row($res)) echo $r[0], '<br>';
?> 

 

However, as it involves creating an array of dates anyway, I'd use option 2, like this

 

<?php 
$mindate = '2008-02-01';
$t = strtotime($mindate);
$dates = array();

while ($t <= time())
{
    $d = date('Y-m-d', $t);
    $dates[$d] = 0;
    $t = strtotime ('+1 days', $t);
}

/**
* Now your original query putting the totals in to the array by date key
*/
$sql = "select Date, count(*) as 'Sales'
        from Transaction
        where productID='5'
        group by Date
        order by Date";
$res = mysql_query($sql);
while (list($d, $v) = mysql_fetch_row($res))
{
    $dates[$d] = $v;
}

foreach ($dates as $$d => $sales)
{
    echo date('d/m/Y', strtotime($d)) . ' ' . $sales . '<br/>';
}
?>

[aschk]

This is a tricky topic, and not one i've ever managed to find a solution for. Creating database results for rows which don't exist (which is essentially the case when trying to produce a zero value) is impossible to my knowledge so the only real way is to use a scripting language to fill the blanks for you.

[Barand]

This is a tricky topic, and not one i've ever managed to find a solution for. Creating database results for rows which don't exist (which is essentially the case when trying to produce a zero value) is impossible to my knowledge so the only real way is to use a scripting language to fill the blanks for you.

 

It's not an unusual problem. It's what LEFT JOINS were made for. (eg list all customers who haven't placed an order iin the last 3 months) In that case the customer table for the left join would exist. The difference here is that it's by date, so table has to created (unless you happen to have a table of all dates hanging around)

[ChrisFlynn]

Two approaches

 

1 ) another table with a row for each date and use "FROM dates LEFT JOIN transaction

 

I think I like the pure SQL approach best. I've now filled up the table; however I'm still only getting:

 

Date  Sales  
2008-02-12 1 
2008-02-14 3 

 

 

Transaction.Date is DATETIME, ref_date is DATE, so I've done a little bit of (ugly) formatting...

 

SELECT date_format( Transaction.Date, '%Y-%m-%d' ) AS 'Date', count( * ) AS 'Sales'
FROM ref_date
LEFT JOIN Transaction ON ref_date.Date = date_format( Transaction.Date, '%Y-%m-%d' ) 
WHERE Transaction.productID = '1'
GROUP BY date_format( Transaction.Date, '%Y-%m-%d' ) 
ORDER BY date_format( Transaction.Date, '%Y-%m-%d' ) 

[ChrisFlynn]

However, as it involves creating an array of dates anyway, I'd use option 2, like this

 

<?php 
$mindate = '2008-02-01';
$t = strtotime($mindate);
$dates = array();

while ($t <= time())
{
    $d = date('Y-m-d', $t);
    $dates[$d] = 0;
    $t = strtotime ('+1 days', $t);
}

/**
* Now your original query putting the totals in to the array by date key
*/
$sql = "select Date, count(*) as 'Sales'
        from Transaction
        where productID='5'
        group by Date
        order by Date";
$res = mysql_query($sql);
while (list($d, $v) = mysql_fetch_row($res))
{
    $dates[$d] = $v;
}

foreach ($dates as $$d => $sales)
{
    echo date('d/m/Y', strtotime($d)) . ' ' . $sales . '<br/>';
}
?>

 

I've also tried this, but get the somewhat cryptic:

 

01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 0
01/01/1970 1
01/01/1970 1
01/01/1970 1
01/01/1970 1

 

I'm starting to wonder if it's something to do with the date format of my tables?

[fenway]

A few things:

 

1) Don't use date_format in GROUP BY / ORDER BY clauses.  Morever, GROUP BY does an implicit ORDER BY.

2) Because your where clause includes a reference to the left-joined table, you've effectively made this an inner join again.  You need to add this condition to your on clause instead.

3) Haven't followed the whole thread, but you should really use proper date column types for all of your dates, not varchars.

 

So:

 

SELECT date_format( Transaction.Date, '%Y-%m-%d' ) AS 'Date', count( * ) AS 'Sales'
FROM ref_date
LEFT JOIN Transaction ON ( ref_date.Date = date_format( Transaction.Date, '%Y-%m-%d' )  AND Transaction.gigID = '1' )
GROUP BY Transaction.Date

[ChrisFlynn]

Thanks for your input.

 

A few things:

 

1) Don't use date_format in GROUP BY / ORDER BY clauses.  Morever, GROUP BY does an implicit ORDER BY.

 

OK - great.

 

2) Because your where clause includes a reference to the left-joined table, you've effectively made this an inner join again.  You need to add this condition to your on clause instead.

 

I see. I've only done inner joins before which is why I'm struggling (I know it's basic!)

 

3) Haven't followed the whole thread, but you should really use proper date column types for all of your dates, not varchars.

 

 

To clarify:

 

Transaction.Date is: datetime NOT NULL (e.g. 2008-02-14 22:41:28)

ref_date.Date is: date NOT NULL (e.g. 2008-02-01)

 

Hence the date_format madness, as I want to group by date, and throw away the H:M:S.

 

 

Ideally I want it in DD/MM/YYYY format, but whatever I can get working at the moment is good enough(!)

 

 

 

 

 

 

So:

 

SELECT date_format( Transaction.Date, '%Y-%m-%d' ) AS 'Date', count( * ) AS 'Sales'
FROM ref_date
LEFT JOIN Transaction ON ( ref_date.Date = date_format( Transaction.Date, '%Y-%m-%d' )  AND Transaction.productID = '1' )
GROUP BY Transaction.Date

 

This outputs the following:

NULL 41 
2008-02-12 1 
2008-02-14 1 
2008-02-14 1 
2008-02-14 1 

 

 

 

Also, doing:

SELECT Transaction.Date AS 'Date', count( * ) AS 'Sales'
FROM ref_date
LEFT JOIN Transaction ON ref_date.Date = Transaction.Date
AND Transaction.productID = '1'
GROUP BY Transaction.Date

 

Gives:

Date  Sales  
NULL 43 

 

 

 

 

I appreciate I haven't made it totally clear from the start, but I appreciate everyone's help!

[fenway]

A few more things...

 

-you should be using ref_date.Date in your select column list, i.e. from the non-left joined table, for the reasons mentioned above.

-you shouldn't use "date" as the column name, it's a reserved keyword, and it works by accident because you have prefixes

-you can use DATE() to just extract the date part of the datetime field; I think mysql might do this silent type converstion for you, but I prefer it to be explicit; date() reads more cleanly

 

 

I also didn't really understand what you wanted until right now.

 

What you need is to  generate  a sequence of dates between the first date and today, and use this in your joins.  You can use an integers tables with date_add() from the initial date.

 

 

 

[ChrisFlynn]

A few more things...

 

-you should be using ref_date.Date in your select column list, i.e. from the non-left joined table, for the reasons mentioned above.

 

If I change my 'Date' column to select from ref_date, then the Sales column won't be counting transactions though? I definitely see the logic that I want every date from ref_date in my left join, but don't see how I can then achieve the sales count without a subquery...

 

 

 

-you shouldn't use "date" as the column name, it's a reserved keyword, and it works by accident because you have prefixes

 

Agreed - will update ASAP. Thanks for the tip.

 

-you can use DATE() to just extract the date part of the datetime field; I think mysql might do this silent type converstion for you, but I prefer it to be explicit; date() reads more cleanly

 

Agreed - much better than dateformat for this.

 

 

I also didn't really understand what you wanted until right now.

 

What you need is to  generate  a sequence of dates between the first date and today, and use this in your joins.  You can use an integers tables with date_add() from the initial date.

 

Yes, I think I'm drifting a bit from my ultimate goal. I want to list the number of sales for each days between the first date there is a sale up to today (including days where there are no sales), ideally formatted DD/MM/YYYY. And I do like keeping it all to SQL if possible.

 

 

 

Here's the latest:

SELECT ref_date.Date AS 'Date', count( * ) AS 'Sales'
FROM ref_date
LEFT JOIN Transaction ON ( ref_date.Date = DATE(Transaction.Date) AND Transaction.productID= '1' )
GROUP BY Transaction.Date

 

2008-01-31 41 
2008-02-12 1 
2008-02-14 1 
2008-02-14 1 
2008-02-14 1 

[fenway]

My bad, your group by should be on ref_date.date, that will eliminate the duplication.

 

But still, you're going to need to generate that date sequence.

[Barand]

 

But still, you're going to need to generate that date sequence.

 

My reply #5, first code block, generates the dates

[fenway]

I meant to generate it on-the-fly in sql using an integers table....