I have the following query:

SELECT DISTINCT
    status,
    CASE
        WHEN status = 0 THEN 'bla' 
        WHEN status = 2 THEN 'bla1'  
    END AS "description" ,
    COUNT(*) AS total     
FROM
    TRANSACTIONS 
WHERE 
    status != 1 
GROUP BY
    status

which displays:

now lets assume I have a table called Status_Codes which provides the Description itself, for example:

I want to remove the case statement from my query that explicitly attaching the descriptions I need, and add my FROM clause the Status_Codes table, and to add Status_Codes.Description to my select. That action cannot be done simply because I use an aggregate function in my select statement and I'd have to group by the same column( which is not something I want). Im not sure on how to approach that problem, was thinking maybe it has something to do with partition by, but even if thats the case I dont know how to implement it in my query.

Any advices, Enlightments and whatnot will be appreciated. thanks.

CodePudding user response：

Why that irrational fear of adding yet another column into the group by clause? That's the simplest and most efficient way of doing it.

  SELECT t.status, c.description, COUNT (*) AS total
    FROM transactions t JOIN status_codes c ON c.status = t.status
   WHERE t.status != 1
GROUP BY t.status, c.description

What benefit do you expect if you do it differently?

BTW, if you have group by clause, you don't need distinct.