CODEWITHSUNDEEP
sqldatabase
Omar Krichen
Omar Krichen

Reputation: 163

SQL: Count items in each category

SELECT items.id, 
       items.category, 
       COUNT(*) 
  FROM items 
 GROUP BY items.id, 
          items.category

enter image description here
I want to display how many items in each category. For example,
category 1 - 6
category 2 - 7
category 3 - 4 ...
Please help me! I try this request and show me all the items with category :/

Upvotes: 3

Views: 11197

Answers (4)

Vitalii Chornobryvyi
Vitalii Chornobryvyi

Reputation: 21

Please use 'distinct'

SELECT distinct items.category, 
       COUNT(*) AS Count 
FROM   items 
GROUP  BY items.category

to get the correct count for each unique category

Upvotes: 2

grishma shah
grishma shah

Reputation: 45

Try this...

select items.category,COUNT(items.COUNT(*)) from items Group By items.category;

Upvotes: 1

DxTx
DxTx

Reputation: 3347

Try this...

SELECT items.category, 
       COUNT(*) AS Count 
FROM   items 
GROUP  BY items.category

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

If, you want to display count based of category then use group by clause with colum category

SELECT category, count(*) as Noofitems
FROM items i
GROUP BY category;

Tiny word of advice :- Use table alise that could be easy to follow and read/wrire

Upvotes: 1

Related Questions