SQL GROUP BY problem

Thread View: comp.databases.ms-access

2 messages

[T] Back to Threads [V] Tree View [G] Group Articles

2 total messages Started by ammar_aganovic Wed, 10 May 2000 00:00

#128

Author: ammar_aganovic
Date: Wed, 10 May 2000 00:00

44 lines
1897 bytes

This is a simplified version of my problem (in reality I need the GROUP
BY orders.id to calculate a price)

select
orders.inv_id,
products.price
from orders
LEFT JOIN products ON products.id = orders.prod_id
GROUP BY orders.id, inv_id

returns:


inv_id | price
---------------------------------------------------------
1      | 13
1      | 50
2      | .
2      | .
2      | .
.      | .


And this is what I would like to get:

inv_id | total
--------------
1      | 63
2      | .
.      | .
.      | .


I need to group the result table by inv_id and sum the sum column - I
thought it would be simple, but I can't seem to get it working. I tried
modifying a GROUP BY line to "GROUP BY orders.id, orders.inv_id" (need
to order by orders.id to calculate a subtotal, and orders.inv_id to
calculate the total) but the rows wouldn't group :( Why?! How can I
perform a GROUP BY ona grouped rows? Documentation?
The DB is MySQL but this bight be a ANSI question.


Sent via Deja.com http://www.deja.com/
Before you buy.

Re: SQL GROUP BY problem

#129

Author: MarkC
Date: Wed, 10 May 2000 00:00

48 lines
2200 bytes

use the sum function to total the price by inv_id.

<ammar_aganovic@my-deja.com> wrote in message
news:8fbq8o$2g1$1@nnrp1.deja.com...
> This is a simplified version of my problem (in reality I need the GROUP
> BY orders.id to calculate a price)
>
> select
> orders.inv_id,
> products.price
> from orders
> LEFT JOIN products ON products.id = orders.prod_id
> GROUP BY orders.id, inv_id
>
> returns:
>
>
> inv_id | price
> ---------------------------------------------------------
> 1      | 13
> 1      | 50
> 2      | .
> 2      | .
> 2      | .
> .      | .
>
>
> And this is what I would like to get:
>
> inv_id | total
> --------------
> 1      | 63
> 2      | .
> .      | .
> .      | .
>
>
> I need to group the result table by inv_id and sum the sum column - I
> thought it would be simple, but I can't seem to get it working. I tried
> modifying a GROUP BY line to "GROUP BY orders.id, orders.inv_id" (need
> to order by orders.id to calculate a subtotal, and orders.inv_id to
> calculate the total) but the rows wouldn't group :( Why?! How can I
> perform a GROUP BY ona grouped rows? Documentation?
> The DB is MySQL but this bight be a ANSI question.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads