CODEWITHSUNDEEP
mysqlsqlselect
Dylan
Dylan

Reputation: 955

How to do a SELECT for total from beginning until the specified date in MySQL?

I have entry table:

enter image description here

I need to do a SELECT to receive 'Date', 'Number of entries' (in that date), 'Total number of entries until that date'.

When I do the SELECT:

SELECT e1.*, 
(select count(*) from entry where date(dateCreated) <= e1.date) as Total 
from (
SELECT 
  DATE(e.dateCreated) as "Date", 
  count(e.dateCreated) as "No of Entries",
  sum( case when e.premium='Y' then 1 else 0 end ) as Premium,
  sum( case when e.free='Y' then 1 else 0 end ) as Free,
  sum( case when e.affiliateID IS NOT NULL then 1 else 0 end) as Affiliate

 FROM entry e
 WHERE e.competitionID=166
 GROUP BY DATE(e.dateCreated)
 ) as e1
ORDER BY Date DESC

I've got a result table

enter image description here

but the column 'Total' has a wrong data.

How the correct select should be? Is this logic of select is the best and more efficient one?

Here is a demo

Upvotes: 2

Views: 81

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

If it is just the 5 vs 7 that is off I think it is because that subquery in your select list, which accesses the inline view e1 (which is filtered to competitionID = 166), is not itself filtered when also utilizing the original entry table (unfiltered). You have to filter the original table to that competitionID as well.

Notice line 3 in sql below (only change)

SELECT e1.*, 
(select count(*) from entry where date(dateCreated) <= e1.date
                              and competitionID=166) as Total 
from (
SELECT 
  DATE(e.dateCreated) as "Date", 
  count(e.dateCreated) as "No of Entries",
  sum( case when e.premium='Y' then 1 else 0 end ) as Premium,
  sum( case when e.free='Y' then 1 else 0 end ) as Free,
  sum( case when e.affiliateID IS NOT NULL then 1 else 0 end) as Affiliate

 FROM entry e
 WHERE e.competitionID=166
 GROUP BY DATE(e.dateCreated)
 ) as e1
ORDER BY Date DESC

Fiddle - http://sqlfiddle.com/#!9/e5e88/22/0

Upvotes: 2

Related Questions