CODEWITHSUNDEEP
sqlmysql
H Varma
H Varma

Reputation: 630

SQL Query- How to fetch record if Group by value is not part of select list table

I have 2 tables similar to this

Table 1 - user table.

U_ID STATUS TR_CODE
1 open NA
2 closed SUC
3 closed CAN
4 closed INV
5 closed SUC
6 closed DEN
5 closed NA
6 closed DEN

Table 2 - lookup table.

LK_CD LK_DSC
SUC Success
CAN Cancelled
INV Invalid
DEN Denied
FAIL Failed

Now I'm trying to get the count of different TR_CODE where STATUS is closed. I need to get the relevant description of TR_CODE from table 2 and group it accordingly

I used below query

SELECT LK_DSC,count(*) as num from user u,lookup where
   LK_CD = u.TR_CODE and u.STATUS = 'closed' GROUP BY  LK_DSC

This is giving me the required data for all TR_CODE that are present in lookup table.But if it is not in lookup table it is not showing(eg: NA is not getting listed)

How to fetch that record as well if not in lookup table and show TR_CODE name itself for NA

Below is the output that Im expecting

LK_DSC     Count

Success      1
Cancelled    1
Invalid      1
Denied       2
NA           2

But with the query I used NA is not getting fetched

Any help would be appreciated

Upvotes: 0

Views: 49

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521989

Use a conditional count instead of COUNT(*) and move the WHERE filter to the count expression:

SELECT
    COALESCE(l.LK_DSC, u.TR_CODE) AS LK_DSC,
    SUM(u.STATUS = 'closed') AS num
FROM user u
LEFT JOIN lookup l
    ON l.LK_CD = u.TR_CODE
GROUP BY 1;

Note also that we use a left join here so as to retain every LK_DSC even if it should have no matching records in the user table.

If you only want to report LK_DSC with a non zero close count, then add a HAVING clause assertion:

SELECT
    COALESCE(l.LK_DSC, u.TR_CODE) AS LK_DSC,
    SUM(u.STATUS = 'closed') AS num
FROM user u
LEFT JOIN lookup l
    ON l.LK_CD = u.TR_CODE
GROUP BY 1
HAVING SUM(u.STATUS = 'closed') > 0;

Upvotes: 0

Related Questions