On Northwind db, all rows coming with grouping

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 2005 Forums

Transact-SQL (2005)

On Northwind db, all rows coming with grouping

Author

Topic

neu84
Starting Member

2 Posts

Posted - 2013-01-23 : 16:17:44

hi,
I'm trying to write a query. I want to display that which product most sold in related year. And i want to show like this,
YEAR, PRODUCTNAME, MAXQUANTITY
1996 Gorgonzola Telino 444
1997 Gnocchi di nonna Alice 971
1998 Konbu 659
But my code (following code) is listing all products' quantity, but i want to see only maximum products in related year.


SELECT ODATE,ProductName,MAXQ FROM Products P INNER JOIN
(
SELECT T.ODATE,T.ProductID,MAX(T.QUANT) AS MAXQ
FROM
( 
	SELECT  YEAR(O.OrderDate) ODATE,ProductID,SUM(QUANTITY) AS QUANT FROM [Order Details] OD
	INNER JOIN Orders O ON O.OrderID=OD.OrderID
	GROUP BY YEAR(O.OrderDate),ProductID
) T
group by T.ODATE,T.ProductID 

) T2 ON P.ProductID=T2.ProductID
ORDER BY ODATE ASC,MAXQ DESC

But if i add

TOP 1 with ties

to first select, its getting only one year info.
YEAR, PRODUCTNAME, MAXQUANTITY
1996 Gorgonzola Telino 444

How can i solve that problem? Can u advice me something please?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-23 : 17:44:18

I think you can do this in SQL 2005

SELECT ODATE,ProductID,QUANT
FROM
(
SELECT YEAR(O.OrderDate) ODATE,ProductID,SUM(QUANTITY) AS QUANT
,[rown] = ROW_NUMBER() over(order by SUM(QUANTITY) desc)
FROM [Order Details] OD
INNER JOIN Orders O ON O.OrderID=OD.OrderID
GROUP BY YEAR(O.OrderDate),ProductID
) t1
WHERE rown = 1

Jim

Everyday I learn something that somebody else already knew

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-23 : 17:47:58

One way would be to change the inner join to a CROSS APPLY like shown below (I don't have Northwind database, so couldn't test this)

SELECT ODATE,
       ProductName,
       MAXQ
FROM   Products P
       CROSS APPLY (
                SELECT TOP (1) T.ODATE,
                       T.ProductID,
                       MAX(T.QUANT) AS MAXQ
                FROM   (
                           SELECT YEAR(O.OrderDate) ODATE,
                                  ProductID,
                                  SUM(QUANTITY) AS QUANT
                           FROM   [Order Details] OD
                                  INNER JOIN Orders O
                                       ON  O.OrderID = OD.OrderID
                           GROUP BY
                                  YEAR(O.OrderDate),
                                  ProductID
                       ) T
                WHERE P.ProductID = T.ProductID
                GROUP BY
                       T.ODATE,
                       T.ProductID
                ORDER BY 
					MAX(T.QUANT) DESC
            ) T2
            
ORDER BY
       ODATE ASC,
       MAXQ DESC

neu84
Starting Member

2 Posts

Posted - 2013-01-24 : 06:35:07

Thank you so much to both of you frieds. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources