CODEWITHSUNDEEP
mysql
Rahul Singh
Rahul Singh

Reputation: 1632

Confusion with query of count

I have a ticket management system in my site. A table named tkt_container in record all responses with 2 columns name ticket id and user id

TICKET ID | USER ID
101       | A
105       | B
103       | A
110       | A
105       | A
101       | A
103       | A
101       | A

Other table dept_info stores ticket info like this.

Dept Name | Ticket ID
Billing   | 101
Billing   | 110
Accounts  | 105
CustomerSu| 103

Now I wish to fetch in this way like department name and total responses..

DEpt Name | Total responses for A
Billing   | 4
Accounts  | 2
CustomerSu| 2

Upvotes: 1

Views: 33

Answers (2)

Marc B
Marc B

Reputation: 360742

SELECT `Dept Name`, COUNT(`USER ID`)
FROM dept_info
LEFT JOIN tkt_container ON tkt_container.`TICKET ID` = dept_info.`Ticket ID`
GROUP BY dept_info.`Dept Name`

I STRONGLY urge you to pick better names for your fields and tables. There's absolutely no valid reason for putting spaces into a field name.

Upvotes: 2

kol
kol

Reputation: 28708

Use a query like this (I wrote it for user 'A'):

select di.dept_name, count(*) as 'Total responses for A'
from  dept_info di inner join tkt_container tc on di.ticket_id = tc.ticket_id
where tc.user_id = 'A'
group by di.dept_name
order by di.dept_name

Upvotes: 0

Related Questions