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?
 
sdstuberCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.