SQL- Getting Subtotal From a Query

[jenn1]

Hi,

I need to run a query where I can get a subtotal for each items in a table.  The query should produce something like:

3 apple

4 grape

1 orange

I did a SUM and COUNT function but, I get the total records (8) in the table instead of the subtotals.

SELECT      id, SUM(fruit) AS Total

FROM         table
WHERE     ORDER BY fruit

Any suggestions would be great.

Thanks,

Jenn

[Dan_Bracuk]

A group by clause in your query might help.  In fact, I'm surprised that it ran without one.

[mgw4jc]

The group by should look like so:

SELECT      COUNT(id), fruit

FROM         table
GROUP BY fruit

[Guest]

try this

SELECT     fruit,count(*) as fruit_count, SUM(fruit) AS Total

FROM         table
WHERE     group BY fruit