multiple 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

General SQL Server Forums

New to SQL Server Programming

multiple GROUP BY

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-31 : 01:19:49

hi,

i would like to know how to perform more than 1 group by in one SELECT. reason is i need to group by 1-to calculate totalx, and perform subgroup to calculate totalq. Say, this is the eg :-

From DB
storageid originqty cartonid
P001 10 C001
P001 10 C002
P001 5 C003
P002 20

Result
Storageid TotalCtnInPallet TotalCarton PcsInCarton
P001 3 2 10
P001 3 1 5
P002 0 0 20

*TotalCtnInPallet = Total of cartonID contain in 1 storageid
*TotalCarton = Total cartonID that has same OriginQty
*PcsInCarton = OriginQty

thanks.. ive tried this using nested select but the result very slow.. like my other topic- temp table or nested select..
so id like to know second opinion to achieve this group by calcualation.. thankss

~~~Focus on problem, not solution~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 01:33:32

[code]
select Storageid,
TotalCtnInPallet = count(cartonid),
TotalCarton = count(distinct OriginQty),
PcsInCarton = OriginQty
from tbl
group by Storageid, OriginQty
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 01:43:41

[code]-- prepare sample data
declare @db table (storageid varchar(4), originqty int, cartonid varchar(4))

insert @db
select 'P001', 10, 'C001' union all
select 'P001', 10, 'C002' union all
select 'P001', 5, 'C003' union all
select 'P002', 20, null

SELECT d1.StorageID,
d2.TotalCtnInPallet,
d1.TotalCarton,
d1.OriginQty
FROM (
SELECT StorageID,
OriginQty,
COUNT(CartonID) AS TotalCarton
FROM @db
GROUP BY StorageID,
OriginQty
) AS d1
INNER JOIN (
SELECT StorageID,
COUNT(CartonID) AS TotalCtnInPallet
FROM @db
GROUP BY StorageID
) AS d2 ON d2.StorageID = d1.StorageID
ORDER BY d1.StorageID,
d1.OriginQty DESC[/code]
Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-02 : 01:35:18

thanks guys.. finally i made my crystal report subtotoal this multiple group for me.. thanks

~~~Focus on problem, not solution~~~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources