What does a multiple count query in SQL return?

Question

I have a product table and every product might be delivered, idle, shipping, preparing.

I want to show a list with the counts of products for each state, and I can see how to query for that here:

How to get multiple counts with one SQL query?

However, what does this query return, and how do I assign the return value to lets say, 4 integers, called deliveredCount, idleCount, shippingCount, preparingCount?

PS: For the record, I am using SQLite with OrmLite in Android with JAVA

EDIT: In this SO question people explain what Query to do when you want to get multiple counts, but they don't tell us what does that query return and in what format. For example:

SELECT a.distributor_id,
    (SELECT COUNT(*) FROM myTable WHERE level='personal' and distributor_id = a.distributor_id) as PersonalCount,
    (SELECT COUNT(*) FROM myTable WHERE level='exec' and distributor_id = a.distributor_id) as ExecCount,
    (SELECT COUNT(*) FROM myTable WHERE distributor_id = a.distributor_id) as TotalCount
FROM myTable a ;

What is the return type of this and what is the format?

PS2: Someone was really quick to downvote my question because it lacked sufficient information. Then I edited it, but the downvote still remains :(

What query do you mean? You only link a Stack Overflow question with two queries and several answers... — Álvaro González, Apr 20 '16 at 09:27
You have to have a sum() with condition in it for each of the states. — Shadow, Apr 20 '16 at 09:32

William · Accepted Answer · 2016-04-20T09:33:03.410

5

Hard to say for sure but sounds like you need to use a version of the top answer in the link you have provided.

Something like;

SELECT ProductID,
    COUNT(*) AS Total,
    SUM(CASE WHEN pStatus = 'delivered' THEN 1 ELSE 0 END) DeliveredCount,
    SUM(CASE WHEN pStatus = 'idle' THEN 1 ELSE 0 END) IdleCount,
    SUM(CASE WHEN pStatus = 'shipping' THEN 1 ELSE 0 END) ShippingCount,
    SUM(CASE WHEN pStatus = 'preparing' THEN 1 ELSE 0 END) PreparingCount
FROM ProductTable
GROUP BY ProductID

This will return something like;

ProductID | DeliveredCount | IdleCount | ...
1         | 250            | 3250      | ...

edited Apr 20 '16 at 09:33

answered Apr 20 '16 at 09:31

William

6,332
8
38
57

My question is what does this query return and in what format? – Kaloyan Roussev Apr 20 '16 at 09:32
Thanks. so how do I extract the values and assign them to integers in Java (or maybe I should post another question for this) – Kaloyan Roussev Apr 20 '16 at 09:36
No worries, yes that would be another question my friend. – William Apr 20 '16 at 09:37
btw how to modify this query if I only have 1 type of product? what should I `SELECT` then? – Kaloyan Roussev Apr 20 '16 at 09:43

score 2 · Answer 2 · answered Apr 20 '16 at 09:38

You might want to try this.

SELECT
SUM(CASE WHEN Prod = 'delivered' THEN 1 ELSE 0 END) as deliveredCount,
SUM(CASE WHEN Prod = 'idle' THEN 1 ELSE 0 END) as idleCount,
SUM(CASE WHEN Prod = 'shipping' THEN 1 ELSE 0 END) as shippingCount,
SUM(CASE WHEN Prod = 'preparing' THEN 1 ELSE 0 END) as preparingCount
FROM Product

Jordan McQueen · Answer 3 · 2016-04-20T09:34:59.433

1

select
  concat(state, "Count"),
  count(*)
from product
group by state

Which would return 4 rows (assuming four unique values of state):

fooCount | 15
etc

edited Apr 20 '16 at 09:34

answered Apr 20 '16 at 09:31

Jordan McQueen

777
5
10

My question is what does this query return and in what format? – Kaloyan Roussev Apr 20 '16 at 09:32
Edited to show example report. It returns a table with two values. – Jordan McQueen Apr 20 '16 at 09:35
Thanks. So how do I extract the values and assign them to integers in Java (or maybe I should post another question for this) – Kaloyan Roussev Apr 20 '16 at 09:36

What does a multiple count query in SQL return?

3 Answers3