Group summary

[mikij]

 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

[kchaudhry]

This sounds like a simple cross tab report. What you want to do is write your query so that it is grouped by the month and has sum of order values. Then build a cross tabl from this query.

Hope this helps!

[mikij]

 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

[Gaby38]

I don't know exactly your problem but you could have a sql query which gives the sum of all the orders by month.

For instance, basically:

Select Month(FDate), sum(price)

group by month(FDate)

But I presume you want something else

[mikij]

 Yes, that's it. But, I just want to show for every month, every Order and summary for that month ?

[kchaudhry]

Insert a group on the month and then show the order details in the "Details" band. For the monthly sum add a total in the group footer.

[mikij]

 Yes, I did it. Thank you.

[mikij]

 Hm...well, it is not so simple, because after month grouping I need to group some Orders in one month too, by the first week, the second week, and the third week for one Customer.

[mikij]

 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 ?

[kchaudhry]

You would write two queries and then join them. An example would be

 

Code:

select * from(your query where value > 1000) ajoin(your query where value < 1000) b on 1=1

[mikij]

 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.

[kchaudhry]

My previous suggestion was based on the assumption that current queries are only returning one row. Based on this if you use a join it would only add columns and not rows. Maybe you can share your query here.

[mikij]

 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!

[kchaudhry]

I am sorry but I just don't understand your requirements well enough. However, if you get one row from this query then all you need to do is write another query for price < 1000 and join them like I mentioned before.

[mikij]

 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.