Union Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Union Question

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2009-06-19 : 23:25:30

Some reason this isn't coming to me today. I usually can get by this issue but maybe it's just lack of sleep.

I have the following

SELECT [User ID],COUNT ([Work Order Number]) AS [Work Order Count]
FROM vwWORDMPF_desc
WHERE [W/O Type]='TC' AND [User ID]= 'User1' AND [Date Entered]='6/15/2009'
GROUP BY [User ID]
UNION

SELECT [User ID],COUNT ([Work Order Number]) AS [Work Order Count]
FROM vwWOMHIPF_desc
WHERE [W/O Type]='TC' AND [User ID]= 'User1' AND [Date Entered]='6/15/2009'
GROUP BY [User ID]

I get the following back

User ID 1 from file 1 COUNT = 1
User ID 1 from file 2 COUNT = 5

This is the basis to the query.

I have several columns that I'm either counting or suming but I only have 3 Columns that I need to group by

Site , User and Date Entered

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-20 : 01:45:24

seems like this is what you're looking at:-


SELECT [User ID],COUNT ([Work Order Number]) AS [Work Order Count]
FROM
(
SELECT [User ID],[Work Order Number]
FROM vwWORDMPF_desc
WHERE [W/O Type]='TC' AND [User ID]= 'User1' AND [Date Entered]='6/15/2009'
GROUP BY [User ID]
UNION 

SELECT [User ID],[Work Order Number]
FROM vwWOMHIPF_desc
WHERE [W/O Type]='TC' AND [User ID]= 'User1' AND [Date Entered]='6/15/2009'
)t
GROUP BY [User ID]

ZMike
Posting Yak Master

110 Posts

Posted - 2009-06-20 : 09:08:33

visakh16,

Thanks ! I'll give it a go later today. Do you happen to know if the same logic also works in Sequel Viewpoint ? I use both at work and at the current time I can't put the end results in SQL Server (we dont have the table from our AS400 Loaded into it yet.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources