On Apr 16, 12:09 pm, Barlymasher <theo...@gmail.com> wrote:
> On Apr 16, 3:01 pm, Barlymasher <theo...@gmail.com> wrote:
>
>
>
> > Hi,
>
> > Seem to be getting stuck, I think there is an easy way to do what I
> > want, I have just not found it yet.
> > I have data for 10 years, I am looking to get a SUM of a field for a
> > particular year.
>
> > For example:
>
> > SELECT SUM(ounces) as ounces
> > FROM beverages
>
> > give me back one result, the total ounces for all 10 years.
>
> > If I do this:
>
> > SELECT date, SUM(ounces) as ounces
> > FROM beverages
> > WHERE YEAR(date) = 1998
> > GROUP BY date
> > ORDER BY date DESC
>
> > I get back multiple rows for the year 1998, when I really only want
> > the one value that is the total SUM for all ounces during the year
> > 1998.
>
> > beverages has 4 fields, location, date, ounces and type.
>
> > thanks for any help!
>
> > O
>
> Also, I have tried
>
> SELECT SUM(ounces) as ounces
> FROM beverages
> WHERE YEAR(date) = 1998
> GROUP BY ounces
>
> O

Just to clarify the "group by" clause -- you can not "sum" and "group
by" on the same column. It doesn't make any sense.

What you can do is "group by" something else. Such as, e.g:

SELECT sum(ounces) FROM beverages
WHERE [other filters]
GROUP BY type

Which might result something like:

type sum(ounces)
Coke 8383
Sprite 1004
OJ 92883

etc. Meaning that, of all the beverages, Coke summed up to 8,383
ounces. Etc.

Actually, if you really only want a single "sum" result for a single
year, you ought to be able to do this:

SELECT SUM(ounces) FROM beverages
WHERE YEAR(date) = 1998

As you describe the problem, "group by" shouldn't be necessary at all.