SQL get group by where count is >= 0

(1) By Bob (bob202) on 2022-04-24 11:36:56 [source]

is there a way SQL query where is am able to get the count of a group even when the count is 0? The query shown below is what I currently have but when the count is 0, it skips past that time interval.

SELECT datetime(round(0.5+julianday(Timestamp)2412)/24/12) AS times, count(Text) as s, ROUND(AVG(Polarity),3) as p FROM words WHERE (strftime('%H:%M:%S',times) BETWEEN '14:30:00' AND '21:00:00') GROUP BY times

Thank you

(2) By midijohnny on 2022-04-24 11:46:51 in reply to 1 [link] [source]

I'm guessing a bit here, because you didn't provide any example data. But - I believe the WHERE clause is filtering out the records you need to take account of.

So - maybe use a HAVING clause instead? (That is, filter after grouping?)

(4.2) By midijohnny on 2022-04-24 12:03:29 edited from 4.1 in reply to 2 [link] [source]

Or maybe a FILTER clause?

-- Simplified data model, same columns name as OP
-- (Temporary table to avoid name-clash)
--
DROP TABLE IF EXISTS temp.words;
CREATE TEMPORARY TABLE words ("Timestamp", "Text", "polarity");
INSERT INTO words VALUES
  (1,'hello',1.5),
  (2,'goodbye',2.5),
  (1,'hello2',3.7),
  (100,'greetings',1.7);

SELECT 
"Timestamp" AS times, 
COUNT("Text") FILTER(WHERE "Timestamp" BETWEEN 1 and 2) AS s,
ROUND(AVG(Polarity),3) AS p FROM words 
GROUP BY times

times	s	p
1	2	2.6
2	1	2.5
100	0	1.7

(3) By John Dennis (jdennis) on 2022-04-24 11:57:45 in reply to 1 [link] [source]

Are you asking for a count of zero for every time which doesn't appear in the source table? If that's the case, there is no set if there are no records in include in it.

If I am trying tyo produce a result like that I start with a temporary table having all the values I wish to include in the output, and then either use an update or a left join with the real data.