Multiple columns at GROUP BY clause in CASE

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)

Multiple columns at GROUP BY clause in CASE

Author

Topic

naunton
Starting Member

4 Posts

Posted - 2010-05-20 : 06:18:20

Dear All,

Please help me, I want to know that can I use multiple columns for GROUP BY clause after using CASE in GROUP BY? Here is my query.
When I execute my query error occour --> Incorrect syntax near ',' <-- at my GROUP BY clause.



declare @num int
set @num= (select ctrqty from ##temp4temp where ctrqty>1)
print @num
SELECT Distinct b.bl_no,b.laden_date , b.container_code , b.shipper_name ,b.consignee_name,sum(b.qty) As FLQty  
FROM docdbdwh.dbo.mastercentral_bl b 
INNER JOIN Credo.dbo.account_code ON b.account_code COLLATE DATABASE_DEFAULT = Credo.dbo.account_code.account_code COLLATE DATABASE_DEFAULT 
inner JOIN ##tmp_ctr2005111852 c ON b.bl_no COLLATE DATABASE_DEFAULT = c.bl_no COLLATE DATABASE_DEFAULT 
	And b.container_code COLLATE DATABASE_DEFAULT = c.container_code COLLATE DATABASE_DEFAULT  
	AND b.vessel_code COLLATE DATABASE_DEFAULT = c.vesselcode COLLATE DATABASE_DEFAULT 
	And b.voyage_no COLLATE DATABASE_DEFAULT = c.voyageno COLLATE DATABASE_DEFAULT 
WHERE ( upper(Credo.dbo.account_code.account_name) like '%FREIGHT%'  and upper(Credo.dbo.account_code.account_name) not like '%FREIGHT REBATE%' ) 
	and b.bl_type <> 'S' and b.bl_no Not In (SELECT bl_no FROM docdbdwh.dbo.MasterCentral_BL WHERE BL_ISSUE = 'IDBDO' AND BL_Type = 'T' )
group by case  @num 
	WHEN  2  THEN 
		(b.bl_no, b.laden_date,b.container_code,b.shipper_name,b.consignee_name,c.status,b.qty) 
	Else 
		(b.bl_no, b.laden_date,b.container_code,b.shipper_name,b.consignee_name)	
	end

Thanks and Best Regards

Honesty is the best policy!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-20 : 06:53:24

quote:
group by case @num
WHEN 2 THEN
(b.bl_no, b.laden_date,b.container_code,b.shipper_name,b.consignee_name,c.status,b.qty)
Else
(b.bl_no, b.laden_date,b.container_code,b.shipper_name,b.consignee_name)
end

you can't do this. You have to use multiple case statement


GROUP BY b.bl_no, b.laden_date, b.container_code, b.shipper_name, b.consignee_name,
         case @num when 2 then c.status end,  
         case @num when 2 then b.qty end

KH
[spoiler]Time is always against us[/spoiler]

naunton
Starting Member

4 Posts

Posted - 2010-05-20 : 21:39:49

Thank you so much, the query work fine with your help.

But, my idiot wrong logic doesn't make result come out what I want.

Honesty is the best policy!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-20 : 22:12:26

that we can't help you unless we know what you want with the query

KH
[spoiler]Time is always against us[/spoiler]

naunton
Starting Member

4 Posts

Posted - 2010-05-20 : 22:49:24

what I want is sum(b.qty)

SELECT Distinct b.bl_no,b.laden_date , b.container_code , b.shipper_name ,b.consignee_name,sum(b.qty) As FLQty

from T1. Have to join with T2.
So I make [code] Inner JOIN T2 on T1.F1=T2.F1 and T1.F2=T2.F2 and T1.F3=T2.F3[code]
Sum(b.qty) work fine for F1,F2,F3 from both Table are same.

But, In T2 I have F4(c.status) that field don't have in T1.
c.status have two type T & F.
What I want sum(b.qty) is to sum c.status(T) one line and c.status(F) one line.

My query recently is sum both c.status(T) and c.status(F) into one line.
That c.status field doesn't have in T1.

Please any idea for this? I am weak in query and in English too :(

Honesty is the best policy!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources