maximum sum --help - 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

Transact-SQL (2000)

maximum sum --help

Author

Topic

tk
Starting Member

1 Post

Posted - 2004-06-16 : 18:59:42

This is my code to find the sum of some orders of products because they can be on mutliple order lines i wanted to know what the total number of orders for each product was. now i need to know what was the highest seller. so i need a maximum of these sums. i have tried multiple approaches and came up with nothing.

SELECT P.product_finish & ' ' & P.product_description AS Product, SUM(OL.quantity) AS Sales
FROM product_t AS P, order_line_t AS OL
WHERE OL.product_ID = P.product_ID
GROUP BY P.product_finish & ' ' & P.product_description
ORDER BY SUM(OL.quantity) DESC;

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-16 : 19:20:35

The & should be a syntax error. Strings are concatenated using plus (+). INNER JOIN may be a better way to go:

SELECT P.product_finish + ' ' + P.product_description AS Product, 
  SUM(OL.quantity) AS Sales
  FROM product_t AS P
  INNER JOIN order_line_t AS OL
    ON OL.product_ID = P.product_ID
GROUP BY P.product_finish & ' ' & P.product_description
ORDER BY SUM(OL.quantity) DESC

To retrieve the maximum "Product" - "TOP N" could be used

SELECT TOP 1 P.product_finish + ' ' + P.product_description AS Product, 
  SUM(OL.quantity) AS Sales
  FROM product_t AS P
  INNER JOIN order_line_t AS OL
    ON OL.product_ID = P.product_ID
GROUP BY P.product_finish & ' ' & P.product_description
ORDER BY SUM(OL.quantity) DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources