SQL Union Showing Duplicates in Result Sets

I have a query that is looking at and "open" and "history" table and combining the query using a UNION.  I am getting the results in each individual result set that I would have expected, but it is showing duplicates in the result sets versus combining the result duplicates into one row.  

Any ideas?  SQL Query and Results below
DECLARE @doc_date DATETIME

SET @doc_date = '1/1/2011'

SELECT  created_by,
		[Week]=DATEPART(ww,doc_date),
        [Orders_Entered] = COUNT(*)
FROM    dbo.tspvSalesDocumentHistory
WHERE   Doc_Date >= @doc_date
        AND Sales_Doc_Type = 'order'
        AND Original_Num=''
GROUP BY Created_By, DATEPART(ww,doc_date)
UNION
SELECT  created_by,
		[Week]=DATEPART(ww,doc_date),
        [Orders_Entered] = COUNT(*)
FROM    dbo.tspvSalesDocument
WHERE   Doc_Date >= @doc_date
        AND Sales_Doc_Type = 'order'
        AND Original_Num=''
GROUP BY Created_By, DATEPART(ww,doc_date)
ORDER BY DATEPART(ww,Doc_Date), Created_By desc


===============================================
                   Results
===============================================
created_by	Week	Orders_Entered
mblack         	11	9
mblack         	11	19
cmoore         	11	24
cmoore         	11	8
cbigbee        	11	5
cbigbee        	11	6
bwalker        	11	59
bwalker        	11	14
bsatterfield1  	11	7
bsatterfield1  	11	4

Open in new window

r270baAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
SELECT  created_by,
            [Week]=DATEPART(ww,doc_date),
        [Orders_Entered] = COUNT(*)
FROM    (select * from dbo.tspvSalesDocumentHistory
union all
select * dbo.tspvSalesDocument) as combined

WHERE   Doc_Date >= @doc_date
        AND Sales_Doc_Type = 'order'
        AND Original_Num=''
GROUP BY Created_By, DATEPART(ww,doc_date)
ORDER BY DATEPART(ww,Doc_Date), Created_By desc
0
 
sdstuberCommented:
try something like this...

union first,  then count

select created_by,
            [Week]=DATEPART(ww,doc_date),
        [Orders_Entered] = COUNT(*)
            from (
SELECT  created_by,
            doc_date
FROM    dbo.tspvSalesDocumentHistory
WHERE   Doc_Date >= @doc_date
        AND Sales_Doc_Type = 'order'
        AND Original_Num=''
UNION
SELECT  created_by,
        doc_date
FROM    dbo.tspvSalesDocument
WHERE   Doc_Date >= @doc_date
        AND Sales_Doc_Type = 'order'
        AND Original_Num='') as combined
GROUP BY Created_By, DATEPART(ww,doc_date)
ORDER BY DATEPART(ww,Doc_Date), Created_By desc

0
 
sdstuberCommented:
you might want to change to UNION ALL instead of UNION if you want the days from each part of the query to be counted individually
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
r270baAuthor Commented:
Msg 8120, Level 16, State 1, Line 5
Column 'dbo.tspvSalesDocumentHistory.Doc_Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
HainKurtSr. System AnalystCommented:
what do you expect?

give sample for first part
give sample for second part

and tell us what do you expect

0
 
r270baAuthor Commented:
I expect the results to not have the names listed twice.  For example, the first two rows of the results show:

created_by      Week      Orders_Entered
mblack               11      9
mblack               11      19

I want that to show:

created_by      Week      Orders_Entered
mblack               11      28
0
 
HainKurtSr. System AnalystCommented:
try this:

select created_by, Week, sum(Orders_Entered) from (
your first query here
UNION ALL
your second query here
) x group by created_by, Week

0
 
HainKurtSr. System AnalystCommented:
ie

select created_by, Week, sum(Orders_Entered) Orders_Entered from (
your original query here with "UNION ALL"
) x group by created_by, Week
0
 
r270baAuthor Commented:
That was close.  Here is the final query:


DECLARE @doc_date DATETIME

SET @doc_date = '1/1/2011'

SELECT  created_by,
        [Week] = DATEPART(ww, doc_date),
        [Orders_Entered] = COUNT(*)
FROM    ( select    Created_By,
                    Doc_Date
          from      tspvSalesDocumentHistory
          WHERE     Doc_Date >= @doc_date
                    AND Sales_Doc_Type = 'order'
                    AND Original_Num = ''
          union all
          select    Created_By,
                    Doc_Date
          FROM      tspvSalesDocument
          WHERE     Doc_Date >= @doc_date
                    AND Sales_Doc_Type = 'order'
                    AND Original_Num = ''
        ) as combined
WHERE   DATEPART(ww, combined.Doc_Date) = DATEPART(ww, GETDATE())
GROUP BY Created_By,
        DATEPART(ww, doc_date)
ORDER BY DATEPART(ww, Doc_Date),
        Created_By

Open in new window

0
 
sdstuberCommented:
the outer criteria should be able to be "pushed" from the outside to the inner, but yes functionally that should be the same
0
All Courses

From novice to tech pro — start learning today.