How to find the number of type 1 and type2 transactions for each client in mysql
I have a table of clients:
id name
1 customer1
2 customer2
3 customer3
and the transaction table:
id customer amount type
1 1 10 type1
2 1 15 type1
3 1 15 type2
4 2 60 type2
5 3 23 type1
I want my query to return is the following table
name type1 type2
customer1 2 1
customer2 0 1
customer3 1 0
Which shows that client1 has made two transactions, transactions of type 1 and 1 of type 2, etc.
Is there a query I can use to get this result, or do I need to use procedural code.
+2
source to share
3 answers
You may try
select c.id as customer_id
, c.name as customer_name
, sum(case when t.`type` = 'type1' then 1 else 0 end) as count_of_type1
, sum(case when t.`type` = 'type2' then 1 else 0 end) as count_of_type2
from customer c
left join `transaction` t
on c.id = t.customer
group by c.id, c.name
This request should only be repeated once per connection.
+2
source to share
SELECT name,
(
SELECT COUNT(*)
FROM transaction t
WHERE t.customer = c.id
AND t.type = 'type1'
) AS type1,
(
SELECT COUNT(*)
FROM transaction t
WHERE t.customer = c.id
AND t.type = 'type2'
) AS type2
FROM customer c
To apply conditions WHERE
to these columns use this:
SELECT name
FROM (
SELECT name,
(
SELECT COUNT(*)
FROM transaction t
WHERE t.customer = c.id
AND t.type = 'type1'
) AS type1,
(
SELECT COUNT(*)
FROM transaction t
WHERE t.customer = c.id
AND t.type = 'type2'
) AS type2
FROM customer c
) q
WHERE type1 > 3
+1
source to share