Skip navigation
jenn
jenn
Currently Being Moderated

SQL- Getting Subtotal From a Query

Sep 30, 2009 9:19 AM

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

864 Views   3 Replies   Latest reply: cafcoder, Oct 7, 2009 11:39 PM
 
Replies
  • Dan Bracuk
    Currently Being Moderated
    1. Dan Bracuk,
    Sep 30, 2009 9:24 AM   in reply to jenn
    Report

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

     
    |
    Mark as:
  • mgw4jc
    Currently Being Moderated
    2. mgw4jc,
    Sep 30, 2009 12:39 PM   in reply to jenn
    Report

    The group by should look like so:

    SELECT      COUNT(id), fruit

    FROM         table
    GROUP BY fruit

     
    |
    Mark as:
  • cafcoder
    Currently Being Moderated
    3. cafcoder,
    Oct 7, 2009 11:39 PM   in reply to jenn
    Report

    try this

     

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

    FROM         table
    WHERE     group BY fruit

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points