Hi,

I need to define one group that will sum some elements in my Orders in some given period (table Order-[1]----[*]->OrderElement). I will always have 3 such orders in a month. I want to do next, when user choose dates e.g., 1.1.2008 to 1.3.2008., I want to show every Order enter in that period, but for every moth, I need to have one summary row that will sum all Order values from that month. How can I do it, any hint ?

My tables:

Order table (id, date, Customer, ...)

OrderElement (id, price, productName, order_id,...)

--

Thanks in advance, Miki

## 14 Answers:

I'm not sure if this is a solution to my problem. My columns are fixed and known.

The report should sum values for every month between two dates I pass, it looks like this:

Orders:

======================================================================

Date | Customer | product name | price |

======================================================================

2008/10/10 | Some Cust. | PRoduct 1 | .... <- this is summary/group for whole Order (includes all its OrderElements)

-----------------------------------------------------------------------------------------------------------------------

2008/10/15 | Some Cust. | PRoduct 1 | ....

-----------------------------------------------------------------------------------------------------------------------

October | Some Cust. | PRoduct 1 | .... summary

-----------------------------------------------------------------------------------------------------------------------

etc.

--

Thanks in advance, Miki

I have one more question (if anyone in this forum wants to help). I need to show group values in one row in my report, but in two columns I need to show different values, e.g. in column1 Orders grouped by some field whose value > 1000 and in column2 Orders whose value < 1000 ?

I'm not sure for this join, because when I apply it I get three times more rows than I should. I want to make simple query, like this:

select column1, column2, sum(column3), sum(column3 when column2 > 1000)

from table

where ...

group by column1, column2

?

Thank you in advance.

I did something with this query below, it return good data, but I'm not sure if such query is good enough (I removed group-aggregate related things):

SELECT order.id, orderelement .id, orderelement.price, P.moreThan1000Price, SUM's(...)

FROM

order INNER JOIN orderelement ON order.id = orderelement.order_id

LEFT JOIN (

select orderelement.id, orderelement.price as moreThan1000Price

FROM

order INNER JOIN orderelement ON order.id = orderelement.order_id

WHERE

SomeCondition and

orderelement.price > 1000

) AS P ON P.id = orderelement.id

WHERE

TheSameConditionAsAbove

GROUP, etc.

?

Thank you in advance!

Hi, I wrote above, my requirement is simple, I want this:

select column1, column2, sum(column3), sum(column3 when column2 > 1000)

from table

where ...

group by column1, column2

I want to sum column3, and the same column3 in cases when (row) e.g., column2 > 1000.

--

Thx.