SQL COUNT help

steroyle · 21 Jan 2012 at 21:50

I need some help please, I have a query that is bringing back data for 2 columns and looks something like this.

Code:

ID    Fruit
----------------
1     Apple
1     Apple
2     Banana
2     Banana
2     Banana
3     Orange
4     Grapes
4     Grapes

How do use the COUNT function so I can display something like this:

Code:

ID    Fruit        Total
--------------------
1     Apple      2
2     Banana     3
3     Orange     1
4     Grapes     2

Everything I am trying keeps bring back 1 row of data.

GravyMonster · 21 Jan 2012 at 21:57

When using aggregate functions you need a Group By clause

Code:

SELECT ID, Fruit, COUNT(ID) AS [Total] FROM FruitsTable GROUP BY ID

I know it's an example, but does the table in your example have a primary key defined? Reason I ask is if 'ID' is the PK you're going to get lots of duplicates which won't be fun. If it's a foreign key, you don't need the 'Fruit' column.

steroyle · 21 Jan 2012 at 22:02

Thank you, that worked perfectly

Ciphon · 21 Jan 2012 at 22:06

PS - Your sig is too tall

steroyle · 21 Jan 2012 at 22:24

Thanks, removed it now.