CODEWITHSUNDEEP
mysqlsql
Nadeem Ijaz
Nadeem Ijaz

Reputation: 599

mysql query to count no of rows in joining three tables and count rows of one table

I have a problem in MYSQL query. I have three tables one is voucher table other clients and third is voucher_client. In voucher_client table I have voucher id column that relate to voucher table and I want to count related rows from client table. Like if voucher table has id 2 and voucher clients are 2 then query will check from client table age_group column where age_group is adult ,child or infant here some pictures of tables for more detail. Please help me out

Voucher table

enter image description here

Client table

enter image description here

Voucher client table

enter image description here

I am trying to do like this 

  SELECT `v`.*, `a`.`name` as `agent_name`, COUNT(CASE WHEN c.age_group = 'Adult' THEN c.id END) AS t_adult, COUNT(CASE WHEN c.age_group = 'Child' THEN c.id END) AS t_child, COUNT(CASE WHEN c.age_group = 'Infant' THEN c.id END) AS t_infant, COUNT(c.id) as total FROM `voucher` `v` JOIN `voucher_client` `vc` ON `vc`.`voucher_id`=`v`.`id` JOIN `client` `c` ON `vc`.`client_id`=`c`.`id` JOIN `tbl_users` `a` ON `a`.`userId`=`v`.`agent_id` LEFT JOIN `voucher_hotel` `vh` ON `vh`.`voucher_id`=`v`.`id` WHERE `v`.`isDeleted` =0 GROUP BY `v`.`id` ORDER BY `v`.`id` DESC

expected output like this

voucher_id t_adult    t_child    t_infant
 1            2           0         0
 2            1           0         0

Upvotes: 0

Views: 104

Answers (1)

LukStorms
LukStorms

Reputation: 29647

If only want to show v.id in the result, then replace v.* by v.id in the query.

(Btw, most databases wouldn't even allow a * when there's group by. MySql deviates from the ANSI SQL standard in that aspect.)

And if you need to join to an extra table with an 1-N relationship? Then you can count the distinct values. So that the totals only reflect the unique clientid's.

SELECT
  v.id AS voucher_id,
  COUNT(DISTINCT CASE WHEN c.age_group = 'Adult'  THEN c.id END) AS t_adult,
  COUNT(DISTINCT CASE WHEN c.age_group = 'Child'  THEN c.id END) AS t_child,
  COUNT(DISTINCT CASE WHEN c.age_group = 'Infant' THEN c.id END) AS t_infant
  -- , COUNT(*) as total
  -- , COUNT(c.id) as total_clientid -- count on value doesn't count NULL's
  -- , COUNT(DISTINCT c.id) as total_unique_clientid
FROM voucher v
JOIN voucher_client vc ON vc.voucher_id = v.id
JOIN client c ON c.id = vc.client_id
-- LEFT JOIN voucher_hotel vh ON vh.voucher_id = v.id
WHERE v.isDeleted = 0
--  AND c.age_group = 'Adult' -- uncomment this to only count the adults
GROUP BY v.id 
ORDER BY v.id

Upvotes: 1

Related Questions