Joining Top 1 to other 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 2008 Forums

Transact-SQL (2008)

Joining Top 1 to other conditions

Author

Topic

k80sg
Starting Member

7 Posts

Posted - 2011-11-18 : 02:06:39

Hi, I have a TOP 1 query(Query2) below which I would like to join it to Query 1 but I can't seem to do something like this:
"select Count(*), Avg(AmtSpent), TOP 1 (Outlet_Code)...."
Please kindly advice. Thanks.

Query 1:
select Count(*), Avg(AmtSpent) from Transact Where CardNo In
(Select CardNo from Card where MemberID = 'Mem003')

Query 2:
select TOP 1 (Outlet_Code) from Transact where CardNo In
(Select CardNo from Card where MemberID = 'Mem003')
group by Outlet_Code
Order by count(Outlet_Code) desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 03:33:29

do you mean this?


select top 1 t.Outlet_Code,count(1) over (),Avg(AmtSpent) over ()
from Transact t
inner join Card c
ON c.CardNo = t.CardNo 
where c.MemberID = 'Mem003'
group by t.Outlet_Code
order by count(*) desc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources