Union with Group By - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Union with Group By

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2009-09-02 : 21:45:43

Here's what I'm trying to accomplish.

Top 5 orders from each of my 4 areas by username with a count of orders. If I do a top 5 I only get 5 records back. Since I cant do a Order By with a Union that gives me some issues too. I could make multiple views with top 5 and union them togeather but I'd think there would be a way to do it in 1 step.

SELECT TOP 20 [Site ID], [Employee Name],SUM([Work Order Count]) AS [Work Order Count]
FROM (

SELECT [Site ID], [Employee Name], COUNT([Work Order Number]) AS [Work Order Count]
FROM dbo.[vwRegional TCs With User Info] AS [vwRegional TCs With User Info_3] WITH (NOLOCK)
WHERE ([Site ID]=1)
GROUP BY [Site ID], [Employee Name]

UNION

SELECT [Site ID], [Employee Name], COUNT([Work Order Number]) AS [Work Order Count]
FROM dbo.[vwRegional TCs With User Info] AS [vwRegional TCs With User Info_3] WITH (NOLOCK)
WHERE ([Site ID]=2)
GROUP BY [Site ID], [Employee Name]

UNION

SELECT [Site ID], [Employee Name], COUNT([Work Order Number]) AS [Work Order Count]
FROM dbo.[vwRegional TCs With User Info] AS [vwRegional TCs With User Info_3] WITH (NOLOCK)
WHERE ([Site ID]=3)
GROUP BY [Site ID], [Employee Name]

UNION

SELECT [Site ID], [Employee Name], COUNT([Work Order Number]) AS [Work Order Count]
FROM dbo.[vwRegional TCs With User Info] AS [vwRegional TCs With User Info_3] WITH (NOLOCK)
WHERE ([Site ID]=4)
GROUP BY [Site ID], [Employee Name]

) A

GROUP BY [Site ID], [Employee Name]

ORDER BY [Work Order Count] DESC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-02 : 23:37:31

[code]
SELECT *
FROM
(
SELECT [Site ID], [Employee Name], COUNT([Work Order Number]) AS [Work Order Count],
row_no = row_number() over (partition by [Site ID], [Employee Name] order by COUNT([Work Order Number]))
FROM dbo.[vwRegional TCs With User Info] AS [vwRegional TCs With User Info_3] WITH (NOLOCK)
WHERE ([Site ID]=1)
GROUP BY [Site ID], [Employee Name]
) a
WHERE a.row_no <= 5
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ZMike
Posting Yak Master

110 Posts

Posted - 2009-09-03 : 15:34:03

khtan,

That didnt work for me I came back with 1400 + records of rowcount =1 Any other ideas ?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-03 : 16:06:03

I think a coupel of tweaks should fix it:

SELECT *
FROM
(
SELECT [Site ID], [Employee Name], COUNT([Work Order Number]) AS [Work Order Count],
        row_no = row_number() over (partition by [Site ID], [Employee Name] order by COUNT([Work Order Number]))
FROM   dbo.[vwRegional TCs With User Info] AS [vwRegional TCs With User Info_3] WITH (NOLOCK)
WHERE [Site ID] BETWEEN 1 AND 4
GROUP BY [Site ID], [Employee Name]
) a
WHERE a.row_no <= 5

ZMike
Posting Yak Master

110 Posts

Posted - 2009-09-03 : 20:59:28

Lamprey,

Thanks, That worked. I had to make a couple minor tweeks to yours to get me the ordering I wanted but it works great. Thank you guys very much

SELECT *
FROM
(
SELECT [Site ID], [Employee Name], COUNT([Work Order Number]) AS [Work Order Count],
row_no = row_number() over (partition by [Site ID] order by COUNT([Work Order Number])DESC)
FROM dbo.[vwRegional TCs With User Info] AS [vwRegional TCs With User Info_3] WITH (NOLOCK)
WHERE [Site ID] BETWEEN 1 AND 4
GROUP BY [Site ID], [Employee Name]
) a
WHERE a.row_no <= 5

ORDER BY [Site ID], [Work Order Count] DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources