CODEWITHSUNDEEP
sqlsqlite
Robert Eickmann
Robert Eickmann

Reputation: 61

SQLite columns most common item in column

I have a table in SQLite that I want to get the item with the most counted elements. And the SQL is driving me batty and giving me the wrong answer.

Table: 

ItemNumber (int) 
totalPurchased (int)

The rows look something like this.

        ItemNumber              TotalPurchased
        1                        7
        1                       10
        1                       20
        2                       40
        3                       32

What I want to get back is an int of 2 for ItemNumber.

Upvotes: 1

Views: 858

Answers (2)

Dmitry Osinovskiy
Dmitry Osinovskiy

Reputation: 10118

If you need to sum everything by ItemNumber, then take max, use this

select ItemNumber from MyTable
group by ItemNumber
order by sum(TotalPurchased) desc
limit 1

If you don't need to sum, try this

select ItemNumber from MyTable
order by TotalPurchased desc
limit 1

Anyway, compare performance of my solution and solution by lc. (that uses max()) and choose the better.

Upvotes: 2

lc.
lc.

Reputation: 116528

You mean like this:

SELECT ItemNumber
FROM myTable
WHERE TotalPurchased = (SELECT MAX(TotalPurchased) FROM myTable)

The key is to think about what you want in plain English. In this case it's "Give me the ItemNumber for the row with the largest TotalPurchased". Unfortunately SQL doesn't understand "with". But rephrasing, we get "Give me the ItemNumber for the row where TotalPurchased is the maximum value of TotalPurchased in the whole table", which is the answer here.

On second thought, I could be misunderstanding you and you want a total of TotalPurchased (TotalPurchased is not a total?)... In that case, try something like:

SELECT t1.ItemNumber, SUM(t1.TotalPurchased) AS TotalTotalPurchased
FROM myTable AS t1
GROUP BY ItemNumber
HAVING SUM(t1.TotalPurchased) =  (
    SELECT SUM(TotalPurchased)
    FROM myTable
    GROUP BY ItemNumber
    ORDER BY SUM(TotalPurchased) DESC  
    LIMIT 1
)

http://sqlfiddle.com/#!5/1cd57/3

Note that these queries have the advantage that if more than one ItemNumber is tied for the maximum, you will get all results. However, if you only want one top result, go with @DmitryOsinovskiy's answer.

Upvotes: 3

Related Questions