Include a WHERE clause in a date query

Question

I'm following the excellent solution from Guido concerning date queries where a given date may not be present in the database https://stackoverflow.com/a/36378448/6591500.

I've got the solution working as is without any issues. I now need expand with this to include a WHERE clause. I have a 'date' column and a 'results' column, I need to count the number of 'passes' on each given day in the results column.

My SQL is this:

SELECT report_date, COUNT(*)-1 AS the_tally FROM (
SELECT date(report_date) as report_date FROM data as t1
WHERE 'spf_result' = 'pass'
UNION ALL 
    SELECT curdate() - interval a day AS report_date FROM (
        select 1 as a union select 2 union select 3 union
        select 4 union select 5 union select 6 union select 7 union
        select 8 union select 9 union select 10 union select 11 union
        select 12 union select 13 union select 14 
    ) as t2 
) as t3 GROUP BY report_date ORDER BY report_date DESC LIMIT 14

The result I'm expecting is:

report_date   The_tally
2020-06-11       4
2020-06-10       4
2020-06-09       6
2020-06-08       4

The result I'm getting is:

report_date   The_tally
2020-06-11       0
2020-06-10       0
2020-06-09       0
2020-06-08       0

Example data is:

report_date   spf_result
2020-06-11       pass
2020-06-11       pass
2020-06-11       pass
2020-06-11       pass
2020-06-10       pass
2020-06-10       pass
2020-06-10       pass
2020-06-10       pass
2020-06-09       pass
etc...

Why am I getting zeros?

This will filter out all records: `WHERE 'spf_result' = 'pass'`, leaving you with zero results... — Luuk, Jun 12 '20 at 15:17

Luuk · Answer 1 · 2020-06-12T15:52:13.707

1

In your query the first part does not return any rows, the result of this:

SELECT date(report_date) as report_date FROM data as t1
WHERE 'spf_result' = 'pass'

is empty because 'spf_result' = 'pass' is never True.

P.S. I do think you mean: WHERE spf_result = 'pass'

edited Jun 12 '20 at 15:52

answered Jun 12 '20 at 15:23

Luuk

12,245
5
22
33

1

Those are probably meant to be back ticks. I'm not smart enough to make them show up in a comment... – Andrew Jun 12 '20 at 15:37
Backticks are only needed if you use reserved words for fieldnames (which you should not do) – Luuk Jun 12 '20 at 15:48
test backticks `\`comment` This is, between backticks a backslash, a backtick and the word 'comment'. – Luuk Jun 12 '20 at 15:49
Backticks. I hadnt noticed both sides had single quotes. I was thinking I'd got the WHERE clause in the wrong location. Its always the simplest solution. – Simon T Jun 12 '20 at 16:29

nbk · Accepted Answer · 2020-07-19T18:30:26.790

You are comparing two strings.

so you have to use backticks

Read also When to use single quotes, double quotes, and backticks in MySQL

SELECT report_date, COUNT(*)-1 AS the_tally FROM (
SELECT date(report_date) as report_date FROM data as t1
WHERE `spf_result` = 'pass'
UNION ALL 
    SELECT curdate() - interval a day AS report_date FROM (
        select 1 as a union select 2 union select 3 union
        select 4 union select 5 union select 6 union select 7 union
        select 8 union select 9 union select 10 union select 11 union
        select 12 union select 13 union select 14 
    ) as t2 
) as t3 GROUP BY report_date ORDER BY report_date DESC LIMIT 14

score 1 · Answer 3 · edited Jun 12 '20 at 16:35

In your query WHERE 'spf_result' = 'pass' change to spf_result='pass' spf_result is your column and your query compare two string and both string are different so your count in "The tally" show 0 because condition('spf_result' = 'pass') return false

i tried in sqlfiddle with given by your example data

DDL :-

CREATE TABLE Table1
    (rdate date,
    status varchar(20)
    );

INSERT INTO Table1
(rdate,status)
VALUES ('2020-06-11','Passed'),('2020-06-11','Passed'),('2020-06-11','Passed'),('2020-06-11','Passed'),('2020-06-10','Passed'),('2020-06-10','Passed'),('2020-06-09','Passed'),('2020-06-10','Passed')
;

Query :-

select rdate,count(*)AS tally from table1 group by rdate order by rdate desc

Output :-

Include a WHERE clause in a date query

3 Answers3