Join two sql queries, with overlapping values, to add a third column

Question

I've read close to a dozen similar issues on this site, but I don't think my specific case was covered anywhere. I'm not a dba, this is just a side-project I was voluntold for because I knew more SQL than anyone else here, so I won't take it personally if somebody tells me this query is a disaster. :-)

This is my query:

SELECT COUNT(a.issue_type_id) as DistinctIssues, b.issue_type_name, c.subissue_type_name, null AS DistinctTickets
FROM [somedb].[dbo].[wh_task] AS a
INNER JOIN [somedb].[dbo].[wh_issue_type] AS b ON a.issue_type_id = b.issue_type_id
INNER JOIN [somedb].[dbo].[wh_subissue_type] AS c ON a.subissue_type_id = c.subissue_type_id
WHERE a.[create_time] between '11/01/12' AND '01/31/13'
AND a.[account_id] = 123456
AND a.[account_contact_id] is not null
GROUP BY b.issue_type_name, c.subissue_type_name  
UNION
SELECT null as DistinctIssues, b.issue_type_name, c.subissue_type_name, COUNT(a.issue_type_id) as DistinctTickets
FROM [somedb].[dbo].[wh_task] AS a
INNER JOIN [somedb].[dbo].[wh_issue_type] AS b ON a.issue_type_id = b.issue_type_id
INNER JOIN [somedb].[dbo].[wh_subissue_type] AS c ON a.subissue_type_id = c.subissue_type_id
WHERE a.[create_time] between '11/01/12' AND '01/31/13'
AND a.[account_id] = 123456
GROUP BY b.issue_type_name, c.subissue_type_name

And the results look like this.

DistinctIssues  issue_type_name   subissue_type_name   DistinctTickets
NULL            Storage           EMC                  45
NULL            Storage           HP                   2
NULL            Symantec          Anti Virus           1
NULL            Symantec          Backup Exec          4
NULL            Virtualization    Environmental        1
NULL            Virtualization    Network              5
1               Microsoft         Server 2003          NULL
1               Microsoft         Windows 7            NULL
1               Network           Performance          NULL
1               Virtualization    Environmental        NULL
2               Exchange          Database             NULL

I bet you can guess what I'm trying to do from here. I'm grouping by the two issue types, and I want the count of all tickets, as well as the count of a subset of those tickets. My database is a MS SQL server (2008, I believe). I only have access to views, for what that's worth.

I've read the post several times and I'm unsure of exactly what you're trying to do. You provided the output of your query, but what would be helpful is to show the output that you'd "like" to get out of the query. Also it looks like your top query is a subset of your bottom query; is this intentional? It looks like the top query will be have the same results as the bottom query minus the records with Null Account_Contact_ID values. I'm still not sure I understand the purpose here. — Eric J. Price
– Eric J. Price, Commented Feb 4, 2013 at 22:44
Sorry, I'll try to be more clear in future, and I'll include desired results as well. — AlsoKnownAsJazz
– AlsoKnownAsJazz, Commented Feb 5, 2013 at 17:21

Andriy M · Accepted Answer · 2013-02-05 00:18:37Z

1

Both queries join the same tables on the same criteria, group the results on the same criteria and even count the same column. The only difference appears to be that one of the queries is using an additional condition in the WHERE clause.

In that case, you can combine them into a single query, using conditional aggregation for the value returned by the query with the additional condition (DistinctIssues):

SELECT
  COUNT(CASE WHEN a.[account_contact_id] is not null THEN a.issue_type_id END) as DistinctIssues,
  b.issue_type_name,
  c.subissue_type_name,
  COUNT(a.issue_type_id) as DistinctTickets
FROM [somedb].[dbo].[wh_task] AS a
INNER JOIN [somedb].[dbo].[wh_issue_type] AS b
  ON a.issue_type_id = b.issue_type_id
INNER JOIN [somedb].[dbo].[wh_subissue_type] AS c
  ON a.subissue_type_id = c.subissue_type_id
WHERE a.[create_time] between '11/01/12' AND '01/31/13'
  AND a.[account_id] = 123456
GROUP BY b.issue_type_name, c.subissue_type_name
;

answered Feb 5, 2013 at 0:18

Andriy M

78.1k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AlsoKnownAsJazz Over a year ago

Awesome, thank you very much! I fiddled with doing a query within a query, but it just eluded me. This worked exactly as I needed it to.

Ian Preston · Accepted Answer · 2013-02-04 22:44:01Z

It's a bit tough to be sure the best way to do this there's a bit of information missing - do you mean something like:

WITH Issues AS
(
  SELECT COUNT(a.issue_type_id) as DistinctIssues, b.issue_type_name, c.subissue_type_name
  FROM [somedb].[dbo].[wh_task] AS a
  INNER JOIN [somedb].[dbo].[wh_issue_type] AS b ON a.issue_type_id = b.issue_type_id
  INNER JOIN [somedb].[dbo].[wh_subissue_type] AS c ON a.subissue_type_id = c.subissue_type_id
  WHERE a.[create_time] between '11/01/12' AND '01/31/13'
  AND a.[account_id] = 123456
  AND a.[account_contact_id] is not null
  GROUP BY b.issue_type_name, c.subissue_type_name
),
Tickets AS
(
  SELECT b.issue_type_name, c.subissue_type_name, COUNT(a.issue_type_id) as DistinctTickets
  FROM [somedb].[dbo].[wh_task] AS a
  INNER JOIN [somedb].[dbo].[wh_issue_type] AS b ON a.issue_type_id = b.issue_type_id
  INNER JOIN [somedb].[dbo].[wh_subissue_type] AS c ON a.subissue_type_id = c.subissue_type_id
  WHERE a.[create_time] between '11/01/12' AND '01/31/13'
  AND a.[account_id] = 123456
  GROUP BY b.issue_type_name, c.subissue_type_name
)
SELECT i.DistinctIssues
  , COALESCE(i.issue_type_name, t.issue_type_name) AS issue_type_name
  , COALESCE(i.subissue_type_name, t.subissue_type_name) AS subissue_type_name
  , t.DistinctTickets
FROM Issues i
  FULL JOIN Tickets t ON i.issue_type_name = i.issue_type_name
    AND i.subissue_type_name = t.subissue_type_name

This gives both issues and tickets for each combination that exists.

If that's not what's required, maybe you could also provide a sample dataset and your desired output?

Collectives™ on Stack Overflow

Join two sql queries, with overlapping values, to add a third column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related