Mysql – thesql query to fetch group wise count

countMySQL

I am having mysql tables like below

tbl_source_1

SRC_ID_1 | SOURCE_NAME_1
1        | ABC
2        | DEF
3        | GHI

tbl_source_2

SRC_ID_2 | SOURCE_NAME_2
1        | NAME_1
2        | NAME_2
3        | NAME_3

tbl_user

user_id | user_nm
1       | JACK
2       | MACK

tbl_data

dta_id | SRC_ID_1 | SRC_ID_2 | user_id | data_name
1      | 1        | 1        | 1       | XYZ
2      | 1        | 1        | 1       | SAD
3      | 2        | 2        | 2       | BAD

now i want COUNT of data (group by source_2) and want result like below 

SOURCE_NAME_1 | user_nm | NAME_1         | NAME_2 | NAME_3
ABC           | JACK    | 2 (it's count) |  0     | 0
DEF           | MACK    | 0              |  1     | 0

Best Answer

group by source_1 and user and count by source_2

Use pivot emulation:

SELECT SOURCE_NAME_1, 
       user_nm, 
       SUM(SOURCE_NAME_2 = 'NAME_1') NAME_1,
       SUM(SOURCE_NAME_2 = 'NAME_2') NAME_2,
       SUM(SOURCE_NAME_2 = 'NAME_3') NAME_3
FROM tbl_data 
LEFT JOIN tbl_user ON ...
LEFT JOIN tbl_source_1 ON ...
LEFT JOIN tbl_source_2 ON ...
GROUP BY SOURCE_NAME_1, user_nm

If SOURCE_NAME_2 values list is not static, use stored procedure which pivot data, or (better) count data only, then pivot it on the client side using client report subsystem.

Related Question