[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How can I combine the results of 2 queries in SQL

Posted on 2016-08-11
9
Medium Priority
?
63 Views
Last Modified: 2016-08-11
I have 2 queries below. Both will produce records in the below format. I would like to combine them, so that the "Closed" column between the 2 is added.

Closedate     CloseUser     Closed
8/1/2016         Bob                  7
8/1/2016         Shelly               9
8/2/2016         Bob                  5...........

The 2 queries are below. I would like to take the sum of both "Closed" columns and add them together. So in the example, If bob had 3 closed from the first query and 4 closed from the 2nd query, they would be added or grouped together to produce the first line.
Can someone assist? I tried using a union, but since I need to "group", I couldn't figure it out.

1st query:
SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE1 LEFT OUTER JOIN
                      Users ON TABLE1.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE1.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE1.CloseUser
ORDER BY  CloseDate, TABLE1.CloseUser

2nd query:
SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' AND Started = 'A' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE2 LEFT OUTER JOIN
                      Users ON TABLE2.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE2.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE2.CloseUser
ORDER BY  CloseDate, TABLE2.CloseUser

Open in new window

0
Comment
Question by:earwig75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41752112
and what do you need to do with the date? do you just need the user and the count?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41752114
that would be:
select ClosePerson, count(Closed)
from (
SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE1 LEFT OUTER JOIN
                      Users ON TABLE1.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE1.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE1.CloseUser

union all 

SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' AND Started = 'A' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE2 LEFT OUTER JOIN
                      Users ON TABLE2.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE2.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE2.CloseUser
) AS A
group by ClosePerson
ORDER BY  ClosePerson

Open in new window

0
 

Author Comment

by:earwig75
ID: 41752156
I need it to return 3 columns, like in my first example....

I made a small mistake, all dates are called "CloseDate" not LastUpdate

Date User Closed
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41752183
so you need to also keep your date as is?

SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE1 LEFT OUTER JOIN
                      Users ON TABLE1.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE1.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE1.CloseUser

union all

SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' AND Started = 'A' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE2 LEFT OUTER JOIN
                      Users ON TABLE2.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE2.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE2.CloseUser

ORDER BY  CloseDate, ClosePerson

Open in new window

0
 

Author Comment

by:earwig75
ID: 41752194
Yes, but I also need to group the Close column, right? I want it to give a sum for each day, for each person.
0
 

Author Comment

by:earwig75
ID: 41752201
it should be 1 row for each user, per date, with a count.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41752204
>>but I also need to group the Close column, right?

which column? the CloseD column? You don't need to group on it, you sum that column grouped by date and user.
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 41752214
I have reread your question. I think your query should be:

select CloseDate, ClosePerson, sum(Closed)
from (
SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE1 LEFT OUTER JOIN
                      Users ON TABLE1.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE1.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE1.CloseUser

union all 

SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' AND Started = 'A' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE2 LEFT OUTER JOIN
                      Users ON TABLE2.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE2.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE2.CloseUser
) AS A
group by CloseDate, ClosePerson
ORDER BY  CloseDate, ClosePerson

Open in new window

0
 

Author Closing Comment

by:earwig75
ID: 41752397
Great, thank you. I was missing the select around my union, and your response solved that. Thanks again!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question