mysql query to fetch group wise count

Question

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

It's pivot. MySQL do not know about it. Emulate by SUM(SOURCE_NAME_2='NAME_x'). — Akina
– Akina, Commented Dec 3, 2018 at 7:53
what are NAME_1,NAME_2,NAME_3 fields i.e. how they are calculating..? — Chandan
– Chandan, Commented Dec 3, 2018 at 8:06
i want COUNT of data (group by source_2) and want result like below As I see you group data by (source_1, user) (?) and pivot (count) by separate source_2 values. — Akina
– Akina, Commented Dec 3, 2018 at 8:25
@Chandan NAME_1 and NAME_2 is the values from tbl_source_2, and calculation - its a count of data that having NAME_1 or NAME_2 id in data table — Manoj
– Manoj, Commented Dec 3, 2018 at 8:31
@Akina Yes, group by source_1 and user and count by source_2 — Manoj
– Manoj, Commented Dec 3, 2018 at 8:32

Akina · Accepted Answer · 2018-12-03 09:09:36Z

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.

Stack Exchange Network

mysql query to fetch group wise count

1 Answer 1

Your Answer

Hot Network Questions

mysql query to fetch group wise count

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions