CODEWITHSUNDEEP
mysqljoingroup-by
Jobz
Jobz

Reputation: 462

MYSQL JOIN query to group user skills for conditional count

I am struggling to find the logic for a JOIN query with GROUP BY.

I have 3 tables.

 1. tbl_users
 2. tbl_event_orders
 3.tbl_event_signature ( For saving signatures on completed events)

tbl_users

id    name     skill
---------------------------
1     user1     A
2     user2     B
3     user3     A
4     user4     A

tbl_orders

id     user_id    item_id    price
------------------------------------
1          1               1                 100
2          2               1                 100
3          3               1                 100
4          4               1                 100

tbl_signature

id     item_id      user_id       signature
----------------------------------------------
1               1                 1                 xxxxxxxx...
1               1                 3                 NULL
1               1                 4                 xxxxxxxx...

I need the event details from item id. For example for item with id 1, I need the following result.

skill         total_count     attended_users_count          amount
A                3               2                          300
B                1               0                          100

skill - skill from user table.

total_count - total count of orders from that partical skill.

attended_users_count - total count of orders from that partical skill + this should have an entry and a NOT NULL value in tbl_signature table.

amount - sum of price(total_count)

I have the following query for getting users with skills and total count.

SELECT 
U.skill as skill, 
count(U.skill) as total_count,
sum( O.price ) as amount
FROM  tbl_users U
INNER JOIN  tbl_orders O
ON U.id = O.user_id
WHERE O.item_id = 1
GROUP BY U.skill

but when dealing with attended users count, I am getting unexpected results. I have tried with the following query,

SELECT 
U.skill as skill, 
count(U.skill) as total_count,
count( S.signature ) as attended_users_count,
sum( O.price ) as amount
FROM  tbl_users U
INNER JOIN  tbl_orders O
ON U.id = O.user_id
LEFT JOIN  tbl_signature S
ON  O.item_id = S.item_id
WHERE O.item_id = 1
GROUP BY U.skill

Is there any way to get this in a single query?

Upvotes: 0

Views: 32

Answers (1)

Nick
Nick

Reputation: 147206

This query should give you the results you want. It JOINs tbl_orders to tbl_users, and then LEFT JOINs to tbl_signature. Rows of tbl_signature which don't match, or which don't have a signature, will not get included in the count for that order:

SELECT u.skill,
       COUNT(o.id) AS total_count,
       COUNT(s.signature) AS attended_users_count,
       SUM(o.price) AS amount
FROM tbl_orders o
JOIN tbl_users u ON u.id = o.user_id
LEFT JOIN tbl_signature s ON s.item_id = o.item_id AND s.user_id = u.id
WHERE o.item_id = 1
GROUP BY u.skill

Output:

skill   total_count attended_users_count    amount
A       3           2                       300
B       1           0                       100

Demo on dbfiddle

Upvotes: 1

Related Questions