understanding how to add a column without having to grouping by it in sql

December 8, 2022

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:

Status	DESCRIPTION	TOTAL
0	bla	29
2	bla1	70
3	(null)	12
4	(null)	85

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

Status	DESCRIPTION
0	bla
2	bla1

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.

>Solution :

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.