January 20, 2012 at 4:46 am
I have a query which uses a CASE WHEN to calculate some value. Inside CAS..END, I have used some fields (eg: 'Commission Per' from a joined table which are NOT IN the SELECT list.
But, when I try to run, getting an error
Column 'Commission Per' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Is it absolute necessary to include these fields also in the GROUP BY clause even if you don't list them?
January 20, 2012 at 4:54 am
meelan (1/20/2012)
I have a query which uses a CASE WHEN to calculate some value. Inside CAS..END, I have used some fields (eg: 'Commission Per' from a joined table which are NOT IN the SELECT list.But, when I try to run, getting an error
Column 'Commission Per' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Is it absolute necessary to include these fields also in the GROUP BY clause even if you don't list them?
Well the column must be in the select list because the error is stating that it is.
January 20, 2012 at 5:10 am
As I stated earlier, it is used inside the CASE..END to calculate a value. Eg:
SELECT S_Person,Sales,(CASE WHEN Sales > 100 THEN (Sales * [Commission Per]) ELSE 0 END) AS Commission FROM SalesTable
Here column [Commission Per] is not displayed or not in the select list.
Thank you
January 20, 2012 at 5:15 am
meelan (1/20/2012)
As I stated earlier, it is used inside the CASE..END to calculate a value. Eg:SELECT S_Person,Sales,(CASE WHEN Sales > 100 THEN (Sales * [Commission Per]) ELSE 0 END) AS Commission FROM SalesTable
Here column [Commission Per] is not displayed or not in the select list.
Thank you
Ok, regardless of whether it is listed by itself, or inside a case statement, the column is still considered to be used in the select statement.
So you need to add the entire case statement to the group clause.
January 20, 2012 at 5:16 am
Or perhaps you intended to Sum() the values from the case statement?
January 20, 2012 at 7:44 am
meelan (1/20/2012)
I have a query which uses a CASE WHEN to calculate some value. Inside CAS..END, I have used some fields (eg: 'Commission Per' from a joined table which are NOT IN the SELECT list.But, when I try to run, getting an error
Column 'Commission Per' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Is it absolute necessary to include these fields also in the GROUP BY clause even if you don't list them?
Hi Meelan,
If your intention is to show the commission when the sale value is higher than 100 then you can use something similar to Method 1.
But if you want to group the S_Person then you have to take the sum of the sales Commission Perc (And yet you can have a condition only to take the commission perc, when the sale is over 100), similar to method 2.
If neither of two is solving your issue, please let know.. 🙂
--=============== Sample Data ==========================
declare @SalesTable as table(
S_Person varchar(4),
Sale money,
[Commision Perc] int
)
insert into @SalesTable
(S_Person, Sale, [Commision Perc])
select '1001',1000,10 union
select '1001',60,10 union
select '1001',800,10 union
select '1001',70,10 union
select '1002',100,10 union
select '1002',180,10 union
select '1002',20,10 union
select '1002',30,10
--================= Method 1 ================================
select S_Person, Sale,
(case when Sale > 100 then (Sale * [Commision Perc]) else 0 end) as [Commision]
from @SalesTable
--================= Method 2 ================================
select S_Person, Sum(Sale),
sum((case when Sale > 100 then (Sale * [Commision Perc]) else 0 end)) as [Commision]
from @SalesTable
group by S_Person
--------
Manjuke
http://www.manjuke.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply