Re: Group By question

Jeff Lanzarotta wrote:
>
> Sam Mason <sam(at)samason(dot)me(dot)uk> wrote: On Tue, Oct 16, 2007 at 07:46:34AM -0700, Jeff Lanzarotta wrote:
>
>>Hello,
>>
>>I have a table that looks something like this:
>>
>>SKU Dept Col1 Col2 Col3
>>------- ------ ------- ------- ------
>>1 1 1 2 3
>>2 1 2 3 4
>>3 2 1 0 1
>>4 2 0 1 2
>>5 2 4 1 3
>>6 3 1 2 3
>>
>>I am having a problem trying to get the Is there a query that can do
>>something like this:
>>
>>select sku, dept, (col1 + col2) * col3) from table group by dept
>
>
> What are you expecting the group by to do here? It may be helpful if
> you show what you expect the output to be.
>
> Okay, actually the query is something like:
>
> select dept, (col1 + col2) * col3) from table group by dept
>
> So, the output would look something like:
>
> Dept Total
> ------ -------
> 1 26
> 2 18
> 3 9
>

Please don't top-post.

The problem may have been that you were selecting SKU (at least, in the
first example). But, as you're aggregating the columns, this is impossible.

SELECT Dept, SUM((Col1 + Col2) * col3) AS total
FROM foo
GROUP BY Dept
ORDER BY Dept;

dept | total
------+-------
1 | 29
2 | 18
3 | 9

(your example had an arithmetic error)

brian