Suppose I have the following three tables: SupportTicket, Device, and Link. I've created these in SQL Fiddle. I want to display a list of support tickets within a time range that meet specified criteria and show a count of the types of devices linked to it. The problem is that my current query is duplicating Support Tickets rather than counting all the devices linked to it on one row. Here is my query:
SELECT st.SupportTicket_ID, st.Submitting_User, st.Submitting_Department, CASE WHEN d.Type = 'Cellphone' THEN COUNT(d.Device_ID) ELSE 0 END AS [Mobile Devices], CASE WHEN d.Type = 'Computer' OR d.Type = 'Disc' THEN COUNT(d.Device_ID) ELSE 0 END AS [Computers], CASE WHEN d.Type = 'Camera' OR d.Type = 'Video Cassette' THEN COUNT(d.Device_ID) ELSE 0 END AS [Audiovisual] FROM SupportTicket st JOIN Link l ON st.SupportTicket_ID = l.Entity_ID1 OR st.SupportTicket_ID = l.Entity_ID2 JOIN Device d ON l.Entity_ID1 = d.Device_ID OR l.Entity_ID2 = d.Device_ID WHERE l.Link_ID LIKE 'CAS%' AND st.Type = 'Tech Support' AND st.Date_Submitted BETWEEN '20140201' AND CURRENT_TIMESTAMP GROUP BY st.SupportTicket_ID, st.Submitting_User, st.Submitting_Department, d.Type ORDER BY st.SupportTicket_ID
A support ticket should only occupy one row with a count of how many devices (by type, as shown) were linked to it in the columns on that row.
Below is the desired output: