SQL Server finding maximum query

Question

me being a humble sql beginner, I have a question: let's say I have two tables: Items, and People(to whom an item is sold). Is there any way in SQL to produce a query to show data of a person who bought the most items, and the ammount of items he or she bought?

my creates are:

CREATE TABLE PEOPLE (
    ID INT PRIMARY KEY, 
    (other data)
);

CREATE TABLE ITEMS (
   ID INT PRIMARY KEY, 
   (other stuff....)
   bought_by INT REFERENCES PEOPLE
);

Any help would be appreciated:)

Szymon · Accepted Answer · 2014-01-07 00:41:10Z

3

Yes, you can just group by bought_by from ITEMS table, sort the result descending by the count and get the top record:

select top 1 bought_by, count(*)
from ITEMS
group by bought_by
order by count(*) desc

answered Jan 7, 2014 at 0:41

Szymon

43k16 gold badges99 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wilson · Accepted Answer · 2014-01-07 00:58:45Z

1

Probably this query can help you too, if you need more people data/info:

SELECT TOP 1 PEOPLE.*, COUNT(ITEMS.*) AMOUNT FROM PEOPLE 
    JOIN ITEMS ON PEOPLE.ID = ITEMS.bought_by
GROUP BY PEOPLE.ID, PEOPLE.OtherFieldsInPeopleTable 
ORDER BY COUNT(ITEMS.*) DESC

answered Jan 7, 2014 at 0:58

Wilson

6095 silver badges16 bronze badges

Collectives™ on Stack Overflow

SQL Server finding maximum query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related