Sort COUNT(CASE WHEN) results

user_17216678 0
Viewed 45

I am taking a database of statuses and creating the statuses as columns in order to count how many records from a network exist in each status. I'd love to sort the results based on the Partnered column DESC, but I can't figure out how or where to do that??

Here's my code:

SELECT type,
    COUNT(CASE WHEN status = "NOT_SUBMITTED" THEN storenumber END) AS Not_Submitted,
    COUNT(CASE WHEN status = "PARTNERED" THEN storenumber END) AS Partnered,
    COUNT(CASE WHEN status = "PENDING" THEN storenumber END) AS Pending,
    COUNT(CASE WHEN status = "SUSPENDED" THEN storenumber END) AS Suspended,
    COUNT(CASE WHEN status = "REJECTED" THEN storenumber END) AS Rejected,
FROM Programs
GROUP BY 1;

Here are my results so far.

row type Not_Submitted Partnered Pending Suspended Rejected
1 abc 26 473 36 0 374
2 def 2481 3943 797 363 1074
3 ghi 0 1965 0 150 102
4 jkl 1231 1851 0 0 0
google-bigquery sql
2 Answers

You just add ORDER BY Partnered DESC as in below example

SELECT type,
    COUNT(CASE WHEN status = "NOT_SUBMITTED" THEN storenumber END) AS Not_Submitted,
    COUNT(CASE WHEN status = "PARTNERED" THEN storenumber END) AS Partnered,
    COUNT(CASE WHEN status = "PENDING" THEN storenumber END) AS Pending,
    COUNT(CASE WHEN status = "SUSPENDED" THEN storenumber END) AS Suspended,
    COUNT(CASE WHEN status = "REJECTED" THEN storenumber END) AS Rejected,
FROM Programs
GROUP BY 1
ORDER BY Partnered DESC

Meantime, consider also below option

SELECT type,
    COUNTIF(status = "NOT_SUBMITTED") AS Not_Submitted,
    COUNTIF(status = "PARTNERED") AS Partnered,
    COUNTIF(status = "PENDING") AS Pending,
    COUNTIF(status = "SUSPENDED") AS Suspended,
    COUNTIF(status = "REJECTED") AS Rejected,
FROM Programs
GROUP BY 1
ORDER BY Partnered DESC

and finally - try below one (it is my preferred option)

SELECT *
FROM (SELECT type, storenumber, status FROM Programs)
PIVOT (
    COUNT(DISTINCT storenumber) 
    FOR status IN ("NOT_SUBMITTED", "PARTNERED", "PENDING", "SUSPENDED", "REJECTED")
)
ORDER BY PARTNERED DESC
user_52219440

Try this:

#standardSQL
WITH table as (
SELECT type,
    COUNT(CASE WHEN status = "NOT_SUBMITTED" THEN storenumber END) AS Not_Submitted,
    COUNT(CASE WHEN status = "PARTNERED" THEN storenumber END) AS Partnered,
    COUNT(CASE WHEN status = "PENDING" THEN storenumber END) AS Pending,
    COUNT(CASE WHEN status = "SUSPENDED" THEN storenumber END) AS Suspended,
    COUNT(CASE WHEN status = "REJECTED" THEN storenumber END) AS Rejected,
FROM Programs
GROUP BY 1)
SELECT * FROM table ORDER BY Partnered DESC

I put your query in a subquery then querying the subquery to be ordered by Partnered DESC

user_26814350
Related