Grouping does not work - data for one column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Grouping does not work - data for one column

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-03 : 05:32:04

Hi, i need help please!

My grouping does not work!, i have 6 measures where the item code will not group for all the measures.

eg:
00001 12 0 0 0 0
00002 0 1 1 1 1

[code]

Select
rtrim(Item_Code) as V_Main
,sum(Act_Jan) as Act_Jan
,sum(Act_Feb) as Act_Feb
,min(List_Excl) as List_Excl
,min(Everyday_Excl) as Everyday_Excl
,min(Nat_Reg_Excl) as Nat_Reg_Excl
,min(National_Excl) as National_Excl

from

(select
rtrim(Item_Code) as Item_Code
,0 as Act_Jan
,0 as Act_Feb
,List_Excl as List_Excl
,Everyday_Excl as Everyday_Excl
,Nat_Reg_Excl as Nat_Reg_Excl
,National_Excl as National_Excl

from dbo.MIS_Marketing_Price

where Period = 2008.02 and Price_Cust_Channel = '606'

Union All

select
rtrim(MCI_CorpItem) as Item_Code
,MCI_Net_Value_R as Act_Jan
,0 as Act_Feb
,0 as List_Excl
,0 as Everyday_Excl
,0 as Nat_Reg_Excl
,0 as National_Excl

from dbo.MIS_MCI_Sales

inner join dbo.MIS_Corp_Customer_Master v1
on MCI_DB = v1.Cust_DB
and MCI_Cust_Code_Srce = v1.Cust_Code_Srce

where MCI_Company = '1'
and MCI_Period = 2008.01 and v1.Cust_CustChannel = '606'

Union All

select
rtrim(MCI_CorpItem) as Item_Code
,0 as Act_Jan
,MCI_Net_Value_R as Act_Feb
,0 as List_Excl
,0 as Everyday_Excl
,0 as Nat_Reg_Excl
,0 as National_Excl

from dbo.MIS_MCI_Sales

inner join dbo.MIS_Corp_Customer_Master v2
on MCI_DB = v2.Cust_DB
and MCI_Cust_Code_Srce = v2.Cust_Code_Srce

where MCI_Company = '1'
and MCI_Period = 2008.02 and v2.Cust_CustChannel = '606'

) a3
group by
rtrim(Item_Code)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-03 : 05:43:59

Please post some sample data, expected output and table structure.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-03 : 05:56:54

Hi, thanks for the help.

Item M1 M2 M3 M4 M5 M6
004518 0 0 2.7416 2.6128 2.5208 2.434
004536 0 0 8.2248 7.8384 7.5624 7.302
004538 129.5 86.4 0 0 0 0
004554 641.0 175.15 0 0 0 0

It looks like there is no values for M1 & M2 item codes
But if i do a select on just those 2 measures - then there is data!

The data should look like:

Item M1 M2 M3 M4 M5 M6
004518 11.1 12.1 2.7416 2.6128 2.5208 2.434
004536 11.1 12.1 8.2248 7.8384 7.5624 7.302
004538 129.5 86.4 11 11 11 11
004554 641.0 175.15 11 11 11 11

Regards

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-03 : 07:07:31

My senior assisted & helped me!

[code]

Select
Item_Code as V_Main
,sum(Act_Jan) as Act_Jan
,sum(Act_Feb) as Act_Feb
,sum(List_Excl) as List_Excl
,sum(Everyday_Excl) as Everyday_Excl
,sum(Nat_Reg_Excl) as Nat_Reg_Excl
,sum(National_Excl) as National_Excl

from
(
select
Item_Code as Item_Code
,0 as Act_Jan
,0 as Act_Feb
,min(List_Excl) as List_Excl
,min(Everyday_Excl) as Everyday_Excl
,min(Nat_Reg_Excl) as Nat_Reg_Excl
,min(National_Excl) as National_Excl

from dbo.MIS_Marketing_Price
where Period = 2008.03 and Price_Cust_Channel not in ('606','607')
group by Item_Code

union all

Select
MCI_CorpItem
,sum(case when MCI_Period=2008.01 then MCI_Net_Value_R else 0 end)
,sum(case when MCI_Period=2008.02 then MCI_Net_Value_R else 0 end)
,0
,0
,0
,0

from dbo.MIS_MCI_Sales(NOLOCK)

left join dbo.MIS_Corp_Customer_Master(NOLOCK)
on cust_db=MCI_DB
and Cust_Code_Srce=MCI_Cust_Code_Srce

where MCI_DB='BPL'
and MCI_Company='1'
and MCI_Period between 2008.01 and 2008.02
and Cust_CustChannel not in ('606','607')

Group by MCI_CorpItem

)q1

group by Item_Code

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources