Calculation by queries

[118600]

Hi Programmers,

Can you please me regarding such an easy problem ? for you, sure its easy but for me, i am stuck.

Serial_Nr Order_Nr Amount Date
1 100 3453 02.06.2004
2 100 532 04.06.2004
3 100 32 01.07.2004
4 120 234 30.07.2004
5 120 235 09.09.04
6 120 2345 10.11.04
7 200 234 10.11.04
8 220 6764 12.12.04
9 300 8956 12.12.04
10 300 4564 25.12.04
11 310 4568 02.01.05
12 400 2244 11.02.05
13 400 37345 20.02.05
14 400 73600 21.02.05


I would like to have the total amount per Order_Nr, per Month and also the total amount for the whole year.


any help ?

Thanks

 

[PHV]

Something like this ?
SELECT Order_Nr, Format(A.Date,'yyyy-mm'), Sum(Amount) As Total
FROM yourTable A
GROUP BY Order_Nr, Format(A.Date,'yyyy-mm')
UNION
SELECT 0, Format(A.Date,'yyyy'), Sum(Amount)
FROM yourTable A
GROUP BY Format(A.Date,'yyyy');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[118600]

Thanks PHV (MIS),

In such a way i am getting order_Nr but i am interested in Total Amount for each Order_Nr.
plus Total Amount for the whole year. is it possible ?

Cheers,

 

[lespaul]

this may work:

SELECT char(Order_Nr), Sum(Amount) As Total
FROM yourTable A
GROUP BY Order_Nr
UNION
SELECT 'Total for Year', Sum(Amount)
FROM yourTable A
GROUP BY Format(A.Date,'yyyy');

should give you the total of each order number and then the total for the year. I added the Char() to order number since I think the format function returns a string. You may have to play around with the first field in order to get the correct syntax.

HTH

Les

 

[PHV]

Sorry, misread your post:
Something like this ?
SELECT Order_Nr & "" As Entity, Sum(Amount) As Total
FROM yourTable A
GROUP BY Order_Nr
UNION
SELECT Format(A.Date,'yyyy-mm'), Sum(Amount)
FROM yourTable A
GROUP BY Format(A.Date,'yyyy-mm')
UNION
SELECT Format(A.Date,'yyyy'), Sum(Amount)
FROM yourTable A
GROUP BY Format(A.Date,'yyyy');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[118600]

i am now trying it with char..... but its not working.
Any way, thanks
If you have any other idea then i will thankful to you

 

[118600]

Cheers PHV (MIS)

I will try it now

 

[118600]

i have tried working on it but in vain. may be i am not good in it all.

Can you please help me ? i try to explain what i am looking for,

1- By giving date limits from this date to this date the total amount is.......

2- by giving date limits from this date to this date the total amount by each Order_Nr is.....

3- the Total Amount a Year For the whole peorid.


Any chance to get this in such steps ?

 

[Reinier10]

Hi,
Just had a quick read of the question, but try replacing the format(date) statements in the sql PHV posted with

datepart("m";[date]), as mMonth, datepart("yyyy";[date]) as mYear

Will try this myself right now.

 

[Reinier10]

Hi again ,
i guess the PHV solution worked already.
Selecting a particular interval can be done bu something like:
SELECT Sum(Table.Amount) AS TotalAmount
FROM

WHERE Table.Date Between [Startdate] And [Enddate];

for the total amount in the interval.

To add the total amount per ordernr add an additional group by , so that would be


SELECT Table.Order, Sum(Table.Amount) AS TotalAmount
FROM

WHERE Table.Date Between [Startdate] And [Enddate]
GROUP BY Table.Order;


hope that helps