Sort COUNT(CASE WHEN) results

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

Answer

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