CODEWITHSUNDEEP
sqlite
Alan2
Alan2

Reputation: 24572

How can I create a report that shows me a count of how many times a value appears in a table?

I have a very simple table that looks like this in SQL:

EnglishCount

0
1
2
1
3
4
6
1
2

Just one column with a value from 0 to 10.

What I would like to do is to create a report that will show me this:

EC      Qty
1       3
2       2
3       1
4       1
6       1

Can someone give me a suggestion how I can achieve this. Note that I would like the value of EC to appear in order and only show a count where rows have an EnglishCount > 0

Upvotes: 0

Views: 43

Answers (3)

bill.lee
bill.lee

Reputation: 2375

select EnglishCount as EC,count(*) as Qty from table group by EnglishCount;

If you want to order by the EnglishCount, 

select EnglishCount as EC,count(*) as Qty from table group by EnglishCount order by EnglishCount;

If you want to order by the count,

select EnglishCount as EC,count(*) as Qty from table group by EnglishCount order by Qty;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521629

This is one correct approach:

SELECT
    EnglishCount AS EC,
    COUNT(*) AS Qty
FROM yourTable
GROUP BY EnglishCount
HAVING COUNT(*) > 0
ORDER BY
    CASE WHEN COUNT(*) = 1 THEN 0 ELSE 1 END,
    EnglishCount

Upvotes: 1

Nithin
Nithin

Reputation: 1414

select EnglishCount as EC,count(*) as Qty from TableName where EnglishCount > 0 group by EnglishCount;

try this...

Upvotes: 0

Related Questions