3

I'm not sure if this is even within the scope of MySQL to be honest or if some php is necessary here to parse the data. But if it is... some kind of stored procedure is likely necessary.

I have a table that stores rows with a timestamp and an amount.

My query is dynamic and will be searching based on a user-provided date range. I would like to retrieve the SUM() of the amounts for each day in a table that are between the date range. including a 0 if there are no entries for a given day

Something to the effect of...

SELECT 
   CASE
     WHEN //there are entries present at a given date
       THEN SUM(amount)
     ELSE 0
   END AS amountTotal,
   //somehow select the day
   FROM  thisTableName T
   WHERE T.timeStamp BETWEEN '$start' AND '$end'
   GROUP BY //however I select the day

This is a two parter...
is there a way to select a section of a returned column? Like some kind of regex within mysql?
Is there a way to return the 0's for dates with no rows?

Derek Adair
  • 21,846
  • 31
  • 97
  • 134
  • *for the returning 0* - perhaps some kind of logic to see if the previous row returned was more than 1 day apart?... i am not sure. It might not be possible. – Derek Adair Jan 28 '10 at 19:16
  • It's possible with a left join, link to an example is in the response below. – St.Woland Jan 28 '10 at 19:21

1 Answers1

2
select * from thisTableName group by date(created_at);

In your case, it would be more like

SELECT id, count(id) as amountTotal
FROM thisTableName
WHERE timeStamp BETWEEN '$start' AND '$end'
GROUP BY DATE(timeStamp);

Your question is a duplicate so far: link.

Community
  • 1
  • 1
St.Woland
  • 5,357
  • 30
  • 30
  • very nice. How would I go about returning zero for dates with no data in them? – Derek Adair Jan 28 '10 at 19:12
  • 1
    I corrected it to `date`, because you will probably have 'multiple months' query. As for dates with no data, this question has already been answered: http://stackoverflow.com/questions/75752/what-is-the-most-straightforward-way-to-pad-empty-dates-in-sql-results-on-either/75928#75928 – St.Woland Jan 28 '10 at 19:17
  • very clever solution! Do you know what - `declare d datetime` do? – Derek Adair Jan 28 '10 at 19:38
  • except it would be SUM(amount) not count(id) – Derek Adair Jan 28 '10 at 19:46