Multiple count() with WHERE clause

Question

I have an SQL table which contains flashcard objects. The table has a column indicated whether a flashcard is free or paid. Flashcards are divided into categories. In my android app I need to display the number of flashcards which are free, and also the number of flashcards which are paid, for each category. If a flashcard isn't free, it is paid.

My SQL isn't great, so far I have a query which returns the number of flashcards which are free:

SELECT _id, category_primary, count(category_primary) FROM Flashcards WHERE available = '1' GROUP BY category_primary;

I want to try to get the count of both free and paid flashcards in a single query/cursor as I display the result in a ListView using an adapter.

Fenton · Answer 1 · 2014-03-14T10:40:14.270

2

You can add the available column to the GROUP BY:

SELECT
    _id,
    category_primary,
    available,
    count(category_primary) 
FROM 
    Flashcards 
GROUP BY
    available,
    category_primary;

As an aside, I would have expected you to need the _id column in both your original query and this updated version - I have left it out because I'm assuming your original query works fine.

edited Mar 14 '14 at 10:40

answered Mar 13 '14 at 02:58

Fenton

241,084
71
387
401

Taking some random `_id` from the original table doesn't make sense in a grouped query. – CL. Mar 13 '14 at 09:05
@CL I can only guess that the `_id` is the primary key that accompanies `category_primary`, in which case I would include it in the `GROUP BY`. – Fenton Mar 13 '14 at 09:49
But a group can have multiple records with the same `category_primary`. – CL. Mar 13 '14 at 10:15
Hi, thanks for your answer. Just to clarify, 'available' is a column which indicates whether a card is free or not. If I add an 'is_paid' column, am I not introducing redundant data? If a flashcard is not free, then it must be paid. – Blake Mumford Mar 14 '14 at 01:42
I made up the column name... I'll edit it to show your actual column name. – Fenton Mar 14 '14 at 10:39
Upvoted, but didn't quite solve my problem. I ended up with 2 rows returned for each category which I couldn't use in an adapter. Thanks for your help though! – Blake Mumford Mar 15 '14 at 05:11

score 0 · Answer 2 · answered Mar 13 '14 at 02:59

select f.category_primary, count(f1._id)as available_count, count(f0._id)as disable_count
from (select _id, category_primary from Flashcards) f 
left join (select _id from Flashcards where available='1') f1 on f1._id=f._id
left join (select _id from Flashcards where available='0') f0 on f0._id=f._id
group by f.category_primary

score 0 · Accepted Answer · edited May 23 '17 at 10:27

Steven Fenton's answer came close, but wasn't quite what I wanted. I found my answer here: How to get multiple counts with one SQL query?.

The query that worked for me is:

SELECT
    _id,
    category_primary,
    sum(CASE WHEN Flashcards.available = '1' THEN 1 ELSE 0 END) category_free,
    sum(CASE WHEN Flashcards.available = '0' THEN 1 ELSE 0 END) category_paid
FROM
    Flashcards
GROUP BY
    category_primary;

The naming of the column available is pretty poor on my part, and I didn't phrase my question very clearly. A better name for the column would be is_free Thanks for those who helped answer my question!

Multiple count() with WHERE clause

3 Answers3