Create query with dynamic number of columns based on a range of dates

Question

I want to create an array using PHP that can be used in MySQL. My query looks like this...

select
    name,
    sum(if (tdate='2009-09-09', amount, 0) as day1,
    sum(if(tdate='2009-09-10', amount, 0) as day2
    ...
    ... 
    sum(if(tdate='2009-10-01', amount, 0) as day23
from revenue
group by name;

The date range may change but always be less than 30 days. If I supply the start and end dates to PHP array will it be able generate the query as shown above?

nickf · Accepted Answer · 2009-09-10 13:30:23Z

2

You should be able to use grouping to do this for you:

SELECT name, DATE(tdate) as d, SUM(amount)
FROM revenue
WHERE d >= NOW() AND d < DATE_ADD(NOW(), 30 DAYS)
GROUP BY name, d

This will give you each date on a new row, instead of multiple fields in the same row, but it's hopefully a workable solution for you.

I assumed that tdate is a datetime. DATE(tdate) will convert it to just a date (dropping the time part). Also, replace the WHERE clause with whatever range you require.

Edit: ok, so we need to have one row per name, with as many fields as needed. This sort of table is called a "crosstab" and MySQL doesn't really support it in a nice way, but you can definitely hack it together. See here for more information: http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html

However, if the dates are known beforehand, it wouldn't be too hard to use PHP to put it all together.

// get this list in whichever way you wish
$dates = array("2009-09-09", "2009-09-10", "2009-09-11");

$sql = "SELECT name, "
     . "sum(if (tdate='"
     . implode("', amount, 0), sum(if (tdate='", $dates)
     . "', amount, 0)"
     . "FROM revenue "
     . "GROUP BY name";

edited Sep 10, 2009 at 13:30

answered Sep 10, 2009 at 13:05

nickf

548k199 gold badges660 silver badges727 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shantanuo Over a year ago

I want the dates to appear in columns. That is the reason I have written the query like that.

acrosman · Accepted Answer · 2009-09-10 13:23:21Z

0

If you'd like to generate the query shown you can use the date function in PHP to help generate the string:

$dateString1 = date("Y-m-d", $date);
$dateString2 = date("Y-m-d", $date2);
$sql = "select
           name,
           sum(if (tdate='$dateString1', amount, 0) as day1,
           sum(if (tdate='$dateString2', amount, 0) as day2
         from revenue
         group by name;";
// Run the query as usual...

answered Sep 10, 2009 at 13:23

acrosman

12.9k10 gold badges42 silver badges56 bronze badges

Collectives™ on Stack Overflow

Create query with dynamic number of columns based on a range of dates

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related