CODEWITHSUNDEEP
sqlsqlitecountcase
c3688014
c3688014

Reputation: 47

Using CASE to set a condition for COUNT statement in SQLite

I have a SELECT statement in SQLite where I need to find the amount of vehicles registered per person in the past year. This is the query:

SELECT
p.fname,
p.lname,
p.bdate,
p.bplace,
COUNT(
  DISTINCT CASE r.vin WHEN r.regdate >= date('now', '-1 years') THEN 1 ELSE null END
),
COUNT(DISTINCT(t.violation))
FROM
persons p
LEFT JOIN registrations r ON p.fname = r.fname
AND p.lname = r.lname
LEFT JOIN tickets t ON r.regno = t.regno
GROUP BY
p.fname,
p.lname,
p.bdate,
p.bplace;

The problem is with the CASE statement, it is not detecting when the condition is true and defaults all my values to 0. How can I fix this, or are there other ways to spcecify a condition in a COUNT statement

Upvotes: 1

Views: 1082

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You could use FILTER(SQLite 3.30.0):

SELECT
   p.fname, p.lname, p.bdate, p.bplace,
   COUNT(DISTINCT r.vin) FILTER (WHERE r.regdate >= date('now', '-1 years')),
   COUNT(DISTINCT(t.violation))
FROM persons p
LEFT JOIN registrations r ON p.fname = r.fname
  AND p.lname = r.lname
LEFT JOIN tickets t ON r.regno = t.regno
GROUP BY p.fname, p.lname, p.bdate,p.bplace;

Upvotes: 4

forpas
forpas

Reputation: 164069

My guess is that you want this:

COUNT(DISTINCT CASE WHEN r.regdate >= date('now', '-1 years') THEN r.vin END)

because COUNT(DISTINCT 1) does not make sense.
ELSE null is not needed.

Upvotes: 0

Related Questions