Home > Archive > PHP SQL > January 2005 > sql problem
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| I have a one table with this colums:
date, article_name, quantity
I interesting how can I select from this table to get
article_name, sum(quantity) for all dates in table
and
article_name, sum(quantity) for one date in one statment.
something like this, but don't know whats next:
select article_name,sum(quantity) as mysum from mytable group by date,
article_name having mysum <> 0 order by article_name;
Sikus
| |
|
|
Sikus:
I'm not sure I have understood what you want; if I have it wrong,
please provide a short example showing the data and the desired
result(s).
To get a summary for a single date, for instance 11-Jan-2005:
SELECT article_name, SUM( quantity ) AS mysum
FROM mytable
WHERE date = 20050111
GROUP BY article_name
HAVING mysum <> 0
ORDER BY article_name
---
Steve
| |
|
| Yes but, is it possible to get result in one statment for one article and
sum quantity for period < 20050111 and sum for day 20050111 ?
Example: I have mytable:
date | article_name | quantity
--------------------------------
'20050111', 'art01', '5'
'20050102', 'art02', '4'
'20050101', 'art01', '1'
'20050102', 'art02', '8'
'20050111', 'art02', '15'
'20050104', 'art01', '4'
and want to get this result:
quantity before '20050111' | quantity on '20050111' | article_name |
---------------------------------------------------------------------------
5 | 5
| art01
12 | 15
| art02
sorry on my bad english, tnx a lot.
Sikus
"Steve" <googlespam@nastysoft.com> wrote in message
news:1105440393.928875.175100@f14g2000cwb.googlegroups.com...
>
> Sikus:
>
> I'm not sure I have understood what you want; if I have it wrong,
> please provide a short example showing the data and the desired
> result(s).
>
> To get a summary for a single date, for instance 11-Jan-2005:
>
> SELECT article_name, SUM( quantity ) AS mysum
> FROM mytable
> WHERE date = 20050111
> GROUP BY article_name
> HAVING mysum <> 0
> ORDER BY article_name
>
> ---
> Steve
>
| |
|
|
Thanks for the extra detail. I can get close, but to get to the exact
output you specified needs some manipulation in the client:
SELECT CASE ( date < 20050111 )
WHEN 1 THEN "1: Before 11-Jan-2005"
WHEN 0 THEN "2: 11-Jan-2005 Only"
END AS group_of_mysum,
article_name, SUM( quantity ) AS mysum
FROM mytable
GROUP BY group_of_mysum, article_name
HAVING mysum <> 0
ORDER BY group_of_mysum, article_name
Gives this result:
group_of_mysum article_name mysum
1: Before 11-Jan-2005 art01 5
1: Before 11-Jan-2005 art02 12
2: 11-Jan-2005 Only art01 5
2: 11-Jan-2005 Only art02 15
You can transpose the results in the client (an exercise left to the
reader 8-).
---
Steve
| |
|
| Thanks a lot, thi is it.
Sikus
"Steve" <googlespam@nastysoft.com> wrote in message
news:1105450949.200734.124510@f14g2000cwb.googlegroups.com...
>
> Thanks for the extra detail. I can get close, but to get to the exact
> output you specified needs some manipulation in the client:
>
> SELECT CASE ( date < 20050111 )
> WHEN 1 THEN "1: Before 11-Jan-2005"
> WHEN 0 THEN "2: 11-Jan-2005 Only"
> END AS group_of_mysum,
> article_name, SUM( quantity ) AS mysum
> FROM mytable
> GROUP BY group_of_mysum, article_name
> HAVING mysum <> 0
> ORDER BY group_of_mysum, article_name
>
>
> Gives this result:
>
>
> group_of_mysum article_name mysum
> 1: Before 11-Jan-2005 art01 5
> 1: Before 11-Jan-2005 art02 12
> 2: 11-Jan-2005 Only art01 5
> 2: 11-Jan-2005 Only art02 15
>
> You can transpose the results in the client (an exercise left to the
> reader 8-).
>
> ---
> Steve
>
|
|
|
|
|