need help in query for group by

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

need help in query for group by

Author

Topic

sonalshastry
Starting Member

3 Posts

Posted - 2009-12-01 : 05:10:24

HI,

I have a bill table with following detail

Client, bill_date,bill_amount, branch ...

I need to fetch data for top 5 client from every branch

below query gives me the data across all branch



SELECT TOP 5 * FROM 
(Select Client_id, branch , sum(bill_amount)amount
from dbo.billtable
where trade_date >= '01 oct 2009' and trade_date <= '31 oct 2009'
group by branch,Client_id)
ORDER BY ter_branch tr DESC

please help

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-01 : 05:16:00

Hope this will help you..

SELECT TOP 5 * FROM
(Select Client_id, branch , sum(bill_amount)amount
from dbo.billtable
where trade_date >= '01 oct 2009' and trade_date <= '31 oct 2009'
group by branch,Client_id) Temp

ORDER BY branch,Client_id DESC

sonalshastry
Starting Member

3 Posts

Posted - 2009-12-01 : 05:37:46

thanks for reply
but this change is resulting in top 5 client from single branch which start with 'A'

I think more clarification in result will help you to understand the issue

Expected output

Client, bill_amount, branch
1 10000 abc
2 9000 abc
3 8000 abc
4 7000 abc
5 6000 abc

11 10100 def
12 9020 def
13 8600 def
14 7400 def
15 6500 def

awaiting reply..

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-01 : 05:55:03

Hi Sonal Shastry,

Here is the query for to achieve the same.

SELECT TOP 5 CLIENT_ID, BRANCH , SUM(BILL_AMOUNT)AMOUNT
FROM DBO.BILLTABLE
WHERE TRADE_DATE >= '01 OCT 2009' AND TRADE_DATE <= '31 OCT 2009'
AND BRANCH='A'--u have to hard code the branch for your need
GROUP BY BRANCH,CLIENT_ID
UNION
SELECT TOP 5 CLIENT_ID, BRANCH , SUM(BILL_AMOUNT)AMOUNT
FROM DBO.BILLTABLE
WHERE TRADE_DATE >= '01 OCT 2009' AND TRADE_DATE <= '31 OCT 2009'
AND BRANCH='B' --u have to hard code the branch for your need
GROUP BY BRANCH,CLIENT_ID
ORDER BY branch,Client_id DESC

Like this u can continue up to entire branch..

sonalshastry
Starting Member

3 Posts

Posted - 2009-12-01 : 06:14:17

Hi,
hard coding of branch is not feasible as there are 900 branches
can some kind of do while loop work in SQL query

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-01 : 07:31:23

Refer this
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources