Multiple columns at GROUP BY clause in CASE clause [modified]

Naunt

Dear CodeProject GURUs, 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

modified on Thursday, May 20, 2010 5:24 AM

Johan Hakkesteegt · modified on Thursday, May 20, 2010 5:24 AM

Apparently it can't be done (not in MS SQL anyway). You may have to build the whole query as a string first, and add the GROUP BY clause separately, before executing it. Something like this:

DECLARE @MainQuery as NVARCHAR(MAX)
DECLARE @GROUPBY as NVARCHAR(MAX)
SET @MainQuery = 'SELECT ItemCode, ItemName FROM OITM '
IF 1 = 1
SET @GROUPBY = 'GROUP BY ItemCode, ItemName'
ELSE
SET @GROUPBY = 'GROUP BY ItemCode, ItemName'

SET @MainQuery = @MainQuery + @GROUPBY

EXEC(@MainQuery)

My advice is free, and you may get what you paid for.

David Skelly

Johan Hakkesteegt wrote:

IF 1 = 1
SET @GROUPBY = 'GROUP BY ItemCode, ItemName'
ELSE
SET @GROUPBY = 'GROUP BY ItemCode, ItemName'

Eh? :confused:

Johan Hakkesteegt

Just an example of a boolean test. I was too lazy to apply it to his code ;) His' would be:

IF @num = 2
SET @GROUPBY = 'GROUP BY (b.bl_no, b.laden_date,b.container_code,b.shipper_name,b.consignee_name,c.status,b.qty)'
ELSE
SET @GROUPBY = 'GROUP BY (b.bl_no, b.laden_date,b.container_code,b.shipper_name,b.consignee_name)'

My advice is free, and you may get what you paid for.

Chris Meech · modified on Thursday, May 20, 2010 5:24 AM

Couple of questions. Why the use of Distinct? This doesn't make sense to me. How can you group by b.qty when it is being summed? This might vary by database, but trying to group by a field (c.status) that is not being selected is usually an error as well. I think you are trying to get two different results sets from similar queries. It might be better to keep the queries separate, or at least until both of them are working. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

Naunt

Thank you so much. The query work fine with your help. :) But, because of my idiot wrong logic the result doesn't make what I want. :(

Naunt

Yes, you are right group by b.qty doesn't make good result. 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

Inner JOIN T2 on T1.F1=T2.F1 and T1.F2=T2.F2 and T1.F3=T2.F3

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. Please any idea for this? I am weak in query. :(

Johan Hakkesteegt

You could look into UNION and UNION ALL. That way you can combine similar queries into a single result set. The basic idea is:

SELECT Field1, SUM(Field2) AS 'Total'
FROM Table1
WHERE Field1 > 0
GROUP BY Field1

UNION ALL

SELECT Field3, SUM(Field4) AS 'Total'
FROM Table2
WHERE Field5 < 1000
GROUP BY Field3

UNION ALL

SELECT Field1, SUM(Field6) AS 'Total'
FROM Table3
WHERE Field8 = 20
GROUP BY Field1

My advice is free, and you may get what you paid for.

Chris Meech

You should be able to keep the select as is (although I still don't see a reason for the Distinct clause), but also include in your select list the c.status field. Also include it in the group by and then you will have a row with all the different status values. And if you only want those "T" and "F" statuses, then add that condition to the where clause. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]