UNION + Group by + some conditions

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 2000 Forums

Transact-SQL (2000)

UNION + Group by + some conditions

Author

Topic

hornet
Yak Posting Veteran

96 Posts

Posted - 2006-03-02 : 04:11:43

Hi! I need to make a query with 3 UNION and to use in everyone UNION GROUP BY AND condtions. How I can do it?
Thanks in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-02 : 04:20:03

Can you please post your table structure, some sample data and expected result ? it will be clearer this way

----------------------------------
'KH'

hornet
Yak Posting Veteran

96 Posts

Posted - 2006-03-02 : 04:29:15

Now i have:

CREATE PROCEDURE xx(
@DateIn SMALLDATETIME,
@DateOut SMALLDATETIME,
@RoomCategoryID INT,
@Building NVARCHAR(8),
@PayerID INT )
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET NOCOUNT ON

BEGIN TRAN
SELECT COUNT(*) AS CountP,
DateIn, DateOut
FROM HPermits
GROUP BY ALL DateIn, DateOut
UNION ALL
SELECT COUNT(*) AS CountP,
DateIn, DateOut
FROM CPermits
GROUP BY ALL DateIn, DateOut
ORDER BY DateIn ASC, DateOut ASC
But i need select only neccessary data on depending of params.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-02 : 04:36:21

Can we have some sample data & your expected result please ? Still not very sure what you are trying to achieve.

----------------------------------
'KH'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-02 : 04:43:37

Or this is what you want ?

select	DateIn, DateOut, sum(cnt)
from
(
	select	DateIn, DateOut, count(*) as cnt
		from	#tbl1
		group by DateIn, DateOut

	union all

	select	DateIn, DateOut, count(*) as cnt
		from	#tbl2
		group by DateIn, DateOut

	union all

	select	DateIn, DateOut, count(*) as cnt
		from	#tbl3
		group by DateIn, DateOut
) as a
group by DateIn, DateOut
order by DateIn, DateOut

----------------------------------
'KH'

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-03-02 : 04:48:41

Looks like you just need to add WHERE clauses to each of your unions (between FROM and GROUP BY).

Mark

hornet
Yak Posting Veteran

96 Posts

Posted - 2006-03-02 : 04:53:41

thanks! I going to try do it.

hornet
Yak Posting Veteran

96 Posts

Posted - 2006-03-02 : 05:30:10

Mark! is working! Thanks a lot!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-02 : 05:34:08

quote:

Originally posted by khtan

Or this is what you want ?

select	DateIn, DateOut, sum(cnt)
from
(
	select	DateIn, DateOut, count(*) as cnt
		from	#tbl1
		group by DateIn, DateOut

	union all

	select	DateIn, DateOut, count(*) as cnt
		from	#tbl2
		group by DateIn, DateOut

	union all

	select	DateIn, DateOut, count(*) as cnt
		from	#tbl3
		group by DateIn, DateOut
) as a
group by DateIn, DateOut
order by DateIn, DateOut

----------------------------------
'KH'

This can be rewritten as



select	DateIn, DateOut, count(*) as counts
from
(
	select	DateIn, DateOut
		from	#tbl1

	union all

	select	DateIn, DateOut
		from	#tbl2

	union all

	select	DateIn, DateOut
		from	#tbl3

) as a
group by DateIn, DateOut
order by DateIn, DateOut

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources