Help With Aggregate Query - 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 2000 Forums

SQL Server Development (2000)

Help With Aggregate Query

Author

Topic

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-10-19 : 02:10:46

Hi everyone,

I Have a table like so :-

Itemcode, Store, qty, Category
------------------------------
123 Site4 11 4401
123 Site4 10 4402
123 Site4 0 4403
123 Site4 1 4404
123 Site4 23 4405

I need an output like :-
Itemcode Storecode Sum(4401) Sum(4402) Sum(4403) Sum(4404) Sum(4405)

123 Site4 11 10 0 1 4405

To acheive this I use a query as : -

SELECT A.ITEMCODE, a.storecode, (SELECT ISNULL(SUM(QTY), 0) FROM TBSPSTORESTOCKSTATUS (NOLOCK)
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4401) AS CATASTK,(SELECT ISNULL(SUM(QTY), 0) FROM TBSPSTORESTOCKSTATUS (NOLOCK)
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4402) AS CATBSTK,
(SELECT ISNULL(SUM(QTY), 0) FROM TBSPSTORESTOCKSTATUS (NOLOCK)
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4403) AS CATCSTK,
(SELECT ISNULL(SUM(QTY), 0) FROM TBSPSTORESTOCKSTATUS (NOLOCK)
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4404) AS CATDSTK,
SUM(QTY) AS TOTALSTK
FROM TBSPSTORESTOCKSTATUS A (nolock)
GROUP BY a.itemcode, a.storecode

This query works. My question is, Is there a better and more efficient way, perhaps using the Case... End statement

Thanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-19 : 02:43:10

you can do it like below

SELECT itemcode,storecode,
SUM(CASE WHEN Category=4401 THEN qty ELSE 0 END) AS [SUM4401],
SUM(CASE WHEN Category=4402 THEN qty ELSE 0 END) AS [SUM4402],
SUM(CASE WHEN Category=4403 THEN qty ELSE 0 END) AS [SUM4403],
SUM(CASE WHEN Category=4404 THEN qty ELSE 0 END) AS [SUM4404],
SUM(CASE WHEN Category=4405 THEN qty ELSE 0 END) AS [SUM4405]
FROM TBSPSTORESTOCKSTATUS  (nolock)
GROUP BY itemcode, storecode

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-10-19 : 02:56:07

Visakh16,

That is very cool. This qry runs much faster than mine. Could you explain why?

Thanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-19 : 03:01:44

quote:
Originally posted by mayoorsubbu

Visakh16,

That is very cool. This qry runs much faster than mine. Could you explain why?

Thanx

because what i've done in my query is a single aggregation and cross tabbed the results. whereas in your queries you use subqueries to get each sum value. subqueries perform very poorly when used on large data tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources