CODEWITHSUNDEEP
mysqljoin
Red Romanov
Red Romanov

Reputation: 474

How to show zero rows for non-existent values

I'm sorry if the title doesn't represent the question as I am not a native English speaker and I don't know how to name this situation. Title recommendation is appreciated.

I have the following query

SELECT mp.PerTPay,bz.BizCode,bz.BizName,count(*) PerCount FROM tb_perbiz bz
LEFT JOIN ms_per mp ON BizCode=mp.PerBiz
GROUP BY bz.BizCode,mp.PerTPay
ORDER BY bz.BizCode,mp.PerTPay

which produces the result

PerTPay  BizCode   BizName  PerCount 
1        01        AAAAA    2
1        02        BBBBB    9
1        03        CCCCC    66
2        03        CCCCC    83
1        04        DDDDD    57
2        04        DDDDD    103

but the following is the result I wanted. I want all possible PerTPay (both 1 and 2) to show for each BizCode.

PerTPay  BizCode   BizName  PerCount 
1        01        AAAAA    2
2        01        AAAAA    0
1        02        BBBBB    9
2        02        BBBBB    0
1        03        CCCCC    66
2        03        CCCCC    83
1        04        DDDDD    57
2        04        DDDDD    103

These are my tables

"ms_per"
PerCode    PerBiz    PerTPay
0001       01        1
0002       01        1
0003       02        1
0004       02        2
and so on

"tb_perbiz"
BizCode   BizName
01        AAAAA
02        BBBBB
03        CCCCC
04        DDDDD

I can't change the main table of the query to ms_per because the query I am showing is reduced from a very large one and it mostly depend on tb_perbiz

Upvotes: 2

Views: 198

Answers (2)

Solarflare
Solarflare

Reputation: 11116

Generally, to get a specific combination in your result, you need to generate that combination first. You can do this if you join your data with a table that contains all allowed values for PerTPay - which is actually the same basic idea why you are left joining to the tb_perbiz-table: a table that contains all allowed values for BizCode. This makes sure you get every code even if it does not exist in ms_per. Otherwise you could just group the table ms_per only.

Since you only have two values, you can generate that table dynamically:

SELECT pt.PerTPay, bz.BizCode, max(bz.BizName) as BizName, 
       count(mp.PerCode) PerCount 
FROM tb_perbiz bz
CROSS JOIN (select 1 as PerTPay union select 2) pt
LEFT JOIN ms_per mp ON bz.BizCode=mp.PerBiz and mp.PerTPay = pt.PerTPay
GROUP BY bz.BizCode,pt.PerTPay
ORDER BY bz.BizCode,pt.PerTPay;

The cross join will give you all possible combinations, wether they exist in ms_per or not.

Note that the grouping is now done on that new "table", and that I changed count(*) to count(mp.PerCode) to make sure to only count existing rows in ms_per. I also added an aggregate function to BizName (max(bz.BizName)), as it is not part of the group by (although I guess that BizCode is your primary key anyway).

Although I used a subquery here to generate the table dynamically, you may want to add an actual table (if you don't have such a table already e.g. to use it in a foreign key). It will e.g. allow you to simply add a 3 there, and your query and all similar queries would produce the expected new result without changing any query code.

Upvotes: 1

Jarek.D
Jarek.D

Reputation: 1294

Try this:

SELECT mp.PerTPay,bz.BizCode,bz.BizName,count(mp.PerTPay) PerCount, count(mp2.PerTPay) PerCount2 FROM tb_perbiz bz
LEFT JOIN ms_per mp ON BizCode=mp.PerBiz AND mp.PerTPay = 1
LEFT JOIN ms_per mp2 ON BizCode=mp2.PerBiz AND mp2.PerTPay = 2
GROUP BY bz.BizCode,mp.PerTPay
ORDER BY bz.BizCode,mp.PerTPay

It won't give you precisely the row layout you presented, but it will give you the counts for PerTPay 1 and PerTPay 2 in separate columns - so essentially you are getting the same information but in slightly different format

Upvotes: 0

Related Questions