Hi!

Could anybody help me, how to sum up a column two ways in one query. I mean:

Code Date(M.D) Weight
EUT 05.15 0,5
COM 05.16 8
COR 05.21 2
SAR 05.22 1

SAR 06.03 0,5
COM 06.03 6
EUT 06.16 6,5
COR 06.16 1

Now I would like to make a query, in which I can sum ip weights for the whle period and for just the month June.

Code Sum(whole period) Sum(just in June)
EUT 7 0,5
COM 14 6
COR 3 6,5
SAR 1,5 1

Is this at all possible?

Thanks in advance:

pontitt

Does this method help http://p2p.wrox.com/topic.asp?TOPIC_ID=12476

Hi pgtip,

I tried the following code in MSQuery:

SELECT `<Table name>`.<Code>, Sum(`<Table name>`.<Weight>) AS 'Whole sum', SUM(CASE WHEN `<Table name>`.<Date(M.D)> >={ts '2004-03-01 00:00:00'} THEN `<Table name>`.<Weight> ELSE 0 END) AS 'Sum just in March'
FROM `\\<pathname>\<filename>`.`<Table name>` `<Table name>`
WHERE (`<Table name>`.<Date(M.D)> >={ts '2004-01-01 00:00:00'})
GROUP BY `<Table name>`.<Code>

But it won't work.

What can be the problem?

pontitt

Can this problem be handled in any way?

Try this it should work

SELECT Code,
          Date(M.D),
          SUM(Weight),
          CASE WHEN Date(M.D) BETWEEN '2004-06-01' AND '2004-06-31'
               THEN SUM(Weight)
               ELSE 0
          END
FROM TableName
GROUP BY Code, Date(M.D)

I put an Excel example relating to this problem at the following Excel topic:

http://www.ozgrid.com/forum/viewthre...age=1#pid50913

called <<Summing2.xls>>

Could somebody help, how to make it work?