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.

CodePudding user response：

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

CodePudding user response：

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