Обсуждение: Group By question

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

Ideas?


-Jeff

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.


  Sam

On 10/16/07, Jeff Lanzarotta <delux256-postgresql@yahoo.com> 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

So, what would the output look like?

For instance, you've got these two lines at the top:
SKU   Dept   Col1   Col2  Col3
-------   ------   -------   -------  ------
1        1        1        2       3
2        1        2        3       4

If we group by dept, then how do I handle those two rows?  Which SKU
would be the right one?  Would the answer be
((sum(col1)+sum(col2))*sum(col3)) ??

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

Sam Mason <sam@samason.me.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.


Sam

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Jeff Lanzarotta wrote:
>
> Sam Mason <sam@samason.me.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