CODEWITHSUNDEEP
sqloracle-database
texmelex
texmelex

Reputation: 87

How to use MAX() and COUNT() in Oracle SQL?

Suppose i have the following table:

transactions(id, type)

id - id of the costumer (integer 1...n)
type - what they bought (like "apple")

Note that each row refers to buying only ONE of the given type.

How can I select the id of the costumer who bought the most of a given type (like apple)?

I tried to COUNT() the rows where type = apple for each id, but I cannot use MAX() in that query to select only the first ID.

Thanks

Upvotes: 0

Views: 1453

Answers (3)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476624

You can first make a query that for every customer counts the amount of times he bought an 'apple', with:

SELECT id,COUNT(*) AS total
FROM transactions
WHERE type = 'apple'
GROUP BY id

Now we only need to ORDER BY that total in DESCending order, and return the first row with FETCH FIRST n ROWS ONLY, like:

SELECT id,COUNT(*) AS total
FROM transactions
WHERE type = 'apple'
GROUP BY id
ORDER BY total DESC
FETCH FIRST 1 ROWS ONLY

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

As you are asking for Oracle in particular: STATS_MODE gives you the value that occurs most often.

select stats_mode(id)
from transactions
where type = 'apple';

Upvotes: 0

Anand
Anand

Reputation: 1123

This is SQL 101, you should read about group by clause:

select id,
    count(*) as count_of_apples
from transactions
where type = 'apple'
group by id
order by count(*) desc

Upvotes: 0

Related Questions