Home » SQL & PL/SQL » SQL & PL/SQL » problem with my pivot table
problem with my pivot table [message #255694] Wed, 01 August 2007 09:06 Go to next message
shuzghun
Messages: 7
Registered: June 2005
Junior Member
Hi i am facing an issue with my pivot table .
when i do a sum of a column's value in the pivot table the total is incorrect .

but when i remove the group by clause the sum is correct .
can anyone please help as this is urgent ?

the query
---
Quote:
select sum(Sales_Forecast_Jan_2008)
from (select
a.ma3_member_code "Customer",
a.produ2_member_code "Material Number",
sum( case when a.ti4_member_code='08M01' then a.Sales0019 else 0 end) Sales_Forecast_Jan_2008,
sum( case when a.ti4_member_code='08M02' then a.Sales0019 else 0 end) Sales_Forecast_Feb_2008,
sum( case when a.ti4_member_code='08M03' then a.Sales0019 else 0 END) Sales_Forecast_Mar_2008,
sum( case when a.ti4_member_code='08M04' then a.Sales0019 else 0 end) Sales_Forecast_Apr_2008,
sum( case when a.ti4_member_code='08M05' then a.Sales0019 else 0 end) Sales_Forecast_May_2008
FROM
md_cube_1 a ,mst_member x,
mst_member_relations b ,
qualitygrp_Matnr c
where
a.ma3_member_code=x.name
and x.level_id=13
and a.produ2_member_code||'-24'=b.MEMBER_ID
and b.level_id='24' and b.parent_level_id='16'
and a.produ2_member_code||'-24'=c.member_id
and a.ti4_member_code >='08M01'
and a.ti4_member_code <='12M12'
group by a.ma3_member_code,
a.produ2_member_code
) ;
Re: problem with my pivot table [message #255699 is a reply to message #255694] Wed, 01 August 2007 09:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case that shows it is incorrect.
Btw, incorrect relative to what? I think it is correct for Oracle and SQL point of view.

Regards
Michel
Re: problem with my pivot table [message #255703 is a reply to message #255699] Wed, 01 August 2007 09:20 Go to previous messageGo to next message
shuzghun
Messages: 7
Registered: June 2005
Junior Member
Michael ,

when i do a sum without grouoing by the result is 933517.356



whereas when i do group by i get

911786.217

and from just doing a simple sum query (without using pivot) i get 933517.356
as result .

For eg ,
query 1 ---
Quote:
select sum(Sales_Forecast_Jan_2008)
from (select
--a.ma3_member_code "Customer",
--a.produ2_member_code "Material Number",
sum( case when a.ti4_member_code='08M01' then a.Sales0019 else 0 end) Sales_Forecast_Jan_2008,
sum( case when a.ti4_member_code='08M02' then a.Sales0019 else 0 end) Sales_Forecast_Feb_2008,
sum( case when a.ti4_member_code='08M03' then a.Sales0019 else 0 END) Sales_Forecast_Mar_2008,
sum( case when a.ti4_member_code='08M04' then a.Sales0019 else 0 end) Sales_Forecast_Apr_2008,
sum( case when a.ti4_member_code='08M05' then a.Sales0019 else 0 end) Sales_Forecast_May_2008
FROM
md_cube_1 a ,mst_member x,
mst_member_relations b ,
qualitygrp_Matnr c
where
a.ma3_member_code=x.name
and x.level_id=13
and a.produ2_member_code||'-24'=b.MEMBER_ID
and b.level_id='24' and b.parent_level_id='16'
and a.produ2_member_code||'-24'=c.member_id
and a.ti4_member_code >='08M01'
and a.ti4_member_code <='12M12'
--group by a.ma3_member_code,
--a.produ2_member_code

gives result --> 933517.356

but query 2 which is the original query gives 911786.217

anyone who can help ??

[mod edit] removed illiterate spelling

[Updated on: Wed, 01 August 2007 12:10] by Moderator

Report message to a moderator

Re: problem with my pivot table [message #255730 is a reply to message #255703] Wed, 01 August 2007 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Center on the problem.
Simplify the query to remove all unnecessary parts till you no more have error.
For instance, you don't need all sum in subquery if you just sum one in outer query...

Regards
Michel
Re: problem with my pivot table [message #255739 is a reply to message #255730] Wed, 01 August 2007 11:12 Go to previous messageGo to next message
shuzghun
Messages: 7
Registered: June 2005
Junior Member
Michael

Sorry but the sum I am doing is to just check the total for one month
the actual query is only the pivot table query .

ie
Quote:
select
a.ma3_member_code "Customer",
a.produ2_member_code "Material Number",
sum( case when a.ti4_member_code='08M01' then a.Sales0019 else 0 end) Sales_Forecast_Jan_2008,
sum( case when a.ti4_member_code='08M02' then a.Sales0019 else 0 end) Sales_Forecast_Feb_2008,
sum( case when a.ti4_member_code='08M03' then a.Sales0019 else 0 END) Sales_Forecast_Mar_2008,
sum( case when a.ti4_member_code='08M04' then a.Sales0019 else 0 end) Sales_Forecast_Apr_2008,
sum( case when a.ti4_member_code='08M05' then a.Sales0019 else 0 end) Sales_Forecast_May_2008
FROM
md_cube_1 a ,mst_member x,
mst_member_relations b ,
qualitygrp_Matnr c
where
a.ma3_member_code=x.name
and x.level_id=13
and a.produ2_member_code||'-24'=b.MEMBER_ID
and b.level_id='24' and b.parent_level_id='16'
and a.produ2_member_code||'-24'=c.member_id
and a.ti4_member_code >='08M01'
and a.ti4_member_code <='12M12'
group by a.ma3_member_code,
a.produ2_member_code
Re: problem with my pivot table [message #255747 is a reply to message #255739] Wed, 01 August 2007 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you remove the outer query, where there is the problem? You lost me completly.
Copy and paste an execution showing it.

Regards
Michel
Re: problem with my pivot table [message #255750 is a reply to message #255747] Wed, 01 August 2007 11:55 Go to previous messageGo to next message
shuzghun
Messages: 7
Registered: June 2005
Junior Member
if i run this query

select
a.ma3_member_code "Customer",
a.produ2_member_code "Material Number",
sum( case when a.ti4_member_code='08M01' then a.Sales0019 else 0 end) Sales_Forecast_Jan_2008,
sum( case when a.ti4_member_code='08M02' then a.Sales0019 else 0 end) Sales_Forecast_Feb_2008,
sum( case when a.ti4_member_code='08M03' then a.Sales0019 else 0 END) Sales_Forecast_Mar_2008,
sum( case when a.ti4_member_code='08M04' then a.Sales0019 else 0 end) Sales_Forecast_Apr_2008,
sum( case when a.ti4_member_code='08M05' then a.Sales0019 else 0 end) Sales_Forecast_May_2008
FROM
md_cube_1 a ,mst_member x,
mst_member_relations b ,
qualitygrp_Matnr c
where
a.ma3_member_code=x.name
and x.level_id=13
and a.produ2_member_code||'-24'=b.MEMBER_ID
and b.level_id='24' and b.parent_level_id='16'
and a.produ2_member_code||'-24'=c.member_id
and a.ti4_member_code >='08M01'
and a.ti4_member_code <='12M12'
group by a.ma3_member_code,
a.produ2_member_code



the results are not correct ...

Re: problem with my pivot table [message #255757 is a reply to message #255750] Wed, 01 August 2007 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Format
2/ Correct regarding to what? What is your meaning of correct? What is your reference point, the one you think is correct?

Regards
Michel
icon8.gif  Re: problem with my pivot table [message #255760 is a reply to message #255757] Wed, 01 August 2007 12:12 Go to previous messageGo to next message
shuzghun
Messages: 7
Registered: June 2005
Junior Member
Michel
I have already told that when the user takes this pivot result and does a sum of each column of this pivot table for sales_forecast , He gets a figure that is wrong .

How he checks ??

he does a simple query in database to get the sum of salesforecast for one month .

i cannot take out the inner sum as they need a pivot table query which gives them the salesforecast for each month in columns.

Hope i was clear .
regards
SHUZ

[Updated on: Wed, 01 August 2007 12:13]

Report message to a moderator

Re: problem with my pivot table [message #255761 is a reply to message #255760] Wed, 01 August 2007 12:21 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not for me.
You can't show me what I asked.
I give up.

Regards
Michel
Previous Topic: PINNED OBJECTS RECOMPILATION
Next Topic: problem adding column values against multiple rows
Goto Forum:
  


Current Time: Sat Dec 03 01:09:39 CST 2016

Total time taken to generate the page: 0.09433 seconds