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.
