How do I get this query 2 work? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How do I get this query 2 work?

Author

Topic

k0001200
Starting Member

7 Posts

Posted - 2004-05-04 : 08:47:38

I am trying to use the function SUN in the following query but it doesnt seem to work.
What am I doing wrong?

SELECT
PACKAGEDGOOD_PRODUCT.ProductCode,
STORE_STOCK_PACKAGED.Store_ID STOREID,
PACKAGEDGOOD_PRODUCT.Description,
PACKAGEDGOOD_PRODUCT.StoreReorder_Level StoreRorderLevel,

sum(STORE_STOCK_PACKAGED.qty_on_shelf+STORE_STOCK_PACKAGED.QTY_IN_STORE)

FROM store_information,store_stock_packaged,PACKAGEDGOOD_PRODUCT
WHERE STORE_INFORMATION.Store_ID=STORE_STOCK_PACKAGED.Store_ID
AND STORE_STOCK_PACKAGED.ProductCode=PACKAGEDGOOD_PRODUCT.ProductCode
AND STORE_STOCK_PACKAGED.Qty_in_Store < PACKAGEDGOOD_PRODUCT.StoreReorder_Level
ORDER BY STORE_STOCK_PACKAGED.Store_ID ;

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-05-04 : 09:06:07

If what I think you are trying to get is....what i'm giving you an answer for....
you need to include

GROUP BY PACKAGEDGOOD_PRODUCT.ProductCode,
STORE_STOCK_PACKAGED.Store_ID STOREID,
PACKAGEDGOOD_PRODUCT.Description,
PACKAGEDGOOD_PRODUCT.StoreReorder_Level StoreRorderLevel

before the ORDER BY

search here (and BOL) for examples of GROUP BY....and what it'll do for you.

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-05-04 : 09:06:55

GROUP BY ??

SELECT
PACKAGEDGOOD_PRODUCT.ProductCode,
STORE_STOCK_PACKAGED.Store_ID STOREID,
PACKAGEDGOOD_PRODUCT.Description,
PACKAGEDGOOD_PRODUCT.StoreReorder_Level StoreRorderLevel,
sum(STORE_STOCK_PACKAGED.qty_on_shelf+STORE_STOCK_PACKAGED.QTY_IN_STORE)
FROM store_information,store_stock_packaged,PACKAGEDGOOD_PRODUCT
WHERE STORE_INFORMATION.Store_ID=STORE_STOCK_PACKAGED.Store_ID
AND STORE_STOCK_PACKAGED.ProductCode=PACKAGEDGOOD_PRODUCT.ProductCode
AND STORE_STOCK_PACKAGED.Qty_in_Store < PACKAGEDGOOD_PRODUCT.StoreReorder_Level
GROUP BY
PACKAGEDGOOD_PRODUCT.ProductCode,
STORE_STOCK_PACKAGED.Store_ID STOREID,
PACKAGEDGOOD_PRODUCT.Description
ORDER BY STORE_STOCK_PACKAGED.Store_ID ;

- Sekar

k0001200
Starting Member

7 Posts

Posted - 2004-05-04 : 09:19:18

quote:
Originally posted by samsekar

GROUP BY ??

SELECT
PACKAGEDGOOD_PRODUCT.ProductCode,
STORE_STOCK_PACKAGED.Store_ID STOREID,
PACKAGEDGOOD_PRODUCT.Description,
PACKAGEDGOOD_PRODUCT.StoreReorder_Level StoreRorderLevel,
sum(STORE_STOCK_PACKAGED.qty_on_shelf+STORE_STOCK_PACKAGED.QTY_IN_STORE)
FROM store_information,store_stock_packaged,PACKAGEDGOOD_PRODUCT
WHERE STORE_INFORMATION.Store_ID=STORE_STOCK_PACKAGED.Store_ID
AND STORE_STOCK_PACKAGED.ProductCode=PACKAGEDGOOD_PRODUCT.ProductCode
AND STORE_STOCK_PACKAGED.Qty_in_Store < PACKAGEDGOOD_PRODUCT.StoreReorder_Level
GROUP BY
PACKAGEDGOOD_PRODUCT.ProductCode,
STORE_STOCK_PACKAGED.Store_ID STOREID,
PACKAGEDGOOD_PRODUCT.Description
ORDER BY STORE_STOCK_PACKAGED.Store_ID ;

If you take out the GROUP BY and ORDER BY, why does the following not work??:

SELECT
PACKAGEDGOOD_PRODUCT.ProductCode,
STORE_STOCK_PACKAGED.Store_ID STOREID,
PACKAGEDGOOD_PRODUCT.Description,
PACKAGEDGOOD_PRODUCT.StoreReorder_Level StoreRorderLevel,

sum(STORE_STOCK_PACKAGED.qty_on_shelf+STORE_STOCK_PACKAGED.QTY_IN_STORE)

FROM store_information,store_stock_packaged,PACKAGEDGOOD_PRODUCT
WHERE STORE_INFORMATION.Store_ID=STORE_STOCK_PACKAGED.Store_ID
AND STORE_STOCK_PACKAGED.ProductCode=PACKAGEDGOOD_PRODUCT.ProductCode
AND STORE_STOCK_PACKAGED.Qty_in_Store < PACKAGEDGOOD_PRODUCT.StoreReorder_Level;

k0001200
Starting Member

7 Posts

Posted - 2004-05-04 : 09:19:20

quote:
Originally posted by samsekar

GROUP BY ??

SELECT
PACKAGEDGOOD_PRODUCT.ProductCode,
STORE_STOCK_PACKAGED.Store_ID STOREID,
PACKAGEDGOOD_PRODUCT.Description,
PACKAGEDGOOD_PRODUCT.StoreReorder_Level StoreRorderLevel,
sum(STORE_STOCK_PACKAGED.qty_on_shelf+STORE_STOCK_PACKAGED.QTY_IN_STORE)
FROM store_information,store_stock_packaged,PACKAGEDGOOD_PRODUCT
WHERE STORE_INFORMATION.Store_ID=STORE_STOCK_PACKAGED.Store_ID
AND STORE_STOCK_PACKAGED.ProductCode=PACKAGEDGOOD_PRODUCT.ProductCode
AND STORE_STOCK_PACKAGED.Qty_in_Store < PACKAGEDGOOD_PRODUCT.StoreReorder_Level
GROUP BY
PACKAGEDGOOD_PRODUCT.ProductCode,
STORE_STOCK_PACKAGED.Store_ID STOREID,
PACKAGEDGOOD_PRODUCT.Description
ORDER BY STORE_STOCK_PACKAGED.Store_ID ;

If you take out the GROUP BY and ORDER BY, why does the following not work??:

SELECT
PACKAGEDGOOD_PRODUCT.ProductCode,
STORE_STOCK_PACKAGED.Store_ID STOREID,
PACKAGEDGOOD_PRODUCT.Description,
PACKAGEDGOOD_PRODUCT.StoreReorder_Level StoreRorderLevel,

sum(STORE_STOCK_PACKAGED.qty_on_shelf+STORE_STOCK_PACKAGED.QTY_IN_STORE)

FROM store_information,store_stock_packaged,PACKAGEDGOOD_PRODUCT
WHERE STORE_INFORMATION.Store_ID=STORE_STOCK_PACKAGED.Store_ID
AND STORE_STOCK_PACKAGED.ProductCode=PACKAGEDGOOD_PRODUCT.ProductCode
AND STORE_STOCK_PACKAGED.Qty_in_Store < PACKAGEDGOOD_PRODUCT.StoreReorder_Level;

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-05-04 : 09:49:39

When you are summing values for all the products, no need for group by clause. Say

Select sum(ProductPrice) from Product where product_catgory = 10

When you want sum of values for each prodct gategory, you need group by clause

Select product_category, sum(ProductPrice) from Product group by product_category.

I think in your query you are not just adding two values.. if so, try this..
SELECT
PACKAGEDGOOD_PRODUCT.ProductCode,
STORE_STOCK_PACKAGED.Store_ID STOREID,
PACKAGEDGOOD_PRODUCT.Description,
PACKAGEDGOOD_PRODUCT.StoreReorder_Level StoreRorderLevel,
STORE_STOCK_PACKAGED.qty_on_shelf+STORE_STOCK_PACKAGED.QTY_IN_STORE
FROM store_information,store_stock_packaged,PACKAGEDGOOD_PRODUCT
WHERE STORE_INFORMATION.Store_ID=STORE_STOCK_PACKAGED.Store_ID
AND STORE_STOCK_PACKAGED.ProductCode=PACKAGEDGOOD_PRODUCT.ProductCode
AND STORE_STOCK_PACKAGED.Qty_in_Store < PACKAGEDGOOD_PRODUCT.StoreReorder_Level;

- Sekar

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-05-04 : 09:52:38

Read BOL (Books OnLine) on Topic 'SELECT' & 'GROUP BY'
Start>Programs>Microsoft SQL Server > BOL

- Sekar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources