0

In pubs database, I want to query the store in which has highest sales quality. I exec following query,

select stores.stor_id, sum(sales.qty) as sumqty
from stores
join sales on stores.stor_id = sales.stor_id
group by stores.stor_id, stores.stor_name

Results:

+-------+------+
|stor_id|sumqty|
+-------+------+
|6380   |8     |
+-------+------+
|7066   |125   |
+-------+------+
|7067   |90    |
+-------+------+
|7131   |130   |
+-------+------+
|7896   |60    |
+-------+------+
|8042   |80    |
+-------+------+

The result I need is like

+-------+------+
|stor_id|sumqty|
+-------+------+
|7131   |130   |
+-------+------+

Could you give some suggestions? Thanks.

KyL
  • 987
  • 12
  • 24

2 Answers2

1

Simple way, use SELECT TOP 1 and ORDER BY DESC

select top 1 sales.stor_id, sum(sales.qty) as sumqty
from stores
join sales on stores.stor_id = sales.stor_id
group by sales.stor_id
order by 2 desc;

Anyway, why you need join to stores? I think from sales table is enough:

select top 1 sales.stor_id, sum(sales.qty) as sumqty
from sales
group by sales.stor_id
order by 2 desc;

Above query will fail if the result has two or more records as max value.

Use this to handle that:

select sales.stor_id, MAX(sumqty) as sumqty from (select sales.stor_id, sum(sales.qty) as sumqty
from sales
group by sales.stor_id) sales
Iswanto San
  • 18,263
  • 13
  • 58
  • 79
  • The reason I join stores is that I want to get store detail information such as store name. You first two queries are work, but as I know, keyword TOP only supported by SQL Server. I hope query is portable. Last query are not work. It fails with message "Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "sales.stor_id" could not be bound." – KyL Jan 16 '14 at 05:52
  • Thanks for your update, but I'm sorry that your newest query still not work with error message "Msg 8120, Level 16, State 1, Line 3 Column 'sales.stor_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." – KyL Jan 16 '14 at 07:52
0
Select a.stor_id, max(sumqty)
From (

select sales.stor_id, sum(sales.qty) as sumqty
from stores
join sales on stores.stor_id = sales.stor_id
group by sales.stor_id

) a 
Group by a.stor_id

Just in case you have two stores with the same sales qty

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JB13
  • 96
  • 4
  • If you post code, XML or data samples, **PLEASE** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Jan 16 '14 at 06:14