I use a query to get customer unpaid amount into different colomns according to due day as the following:
Select c.credit_controller,c.customer,c.name,i.unall_amount,
CASE /*cash due*/
when i.kind='CSH' then i.unall_amount
else 0
end as amtcash,
CASE /*sales_item.due_date - today < 0 */
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate()) < 0)
THEN i.unall_amount
ELSE 0
END AS amtcur,
CASE /*sales_item.due_date - today > 0 & <= 30*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 0 AND 30) THEN i.unall_amount
ELSE 0
END AS amt30,
CASE /*sales_item.due_date - today > 30 & <= 60*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 31 AND 60) THEN i.unall_amount
ELSE 0
END AS amt60,
CASE /*sales_item.due_date - today > 60 & <= 90*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 61 AND 90) THEN i.unall_amount
ELSE 0
END AS amt90,
CASE /*sales_item.due_date - today > 90*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
> 90) THEN i.unall_amount
ELSE 0
END AS amt120,
c.analysis_codes
From scheme.slcustm c INNER JOIN scheme.slitemm i ON c.customer=i.customer
I want to SUM those records and GROUP BY c.credit_controller & c.customer in a SELECT query.
If I use SUM(i.unall_amount) instead of i.unall_amount, there're errors as the following:
Column 'c.cusomter' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'i.kind' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column c.analaysis_code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
How to SUM in this query?
Thanks!
yabing
When you use GROUP BY statetment, then any column used in select list must be also GROUP BY list or under some aggregation function.
So if you want GROUP BY by c.credit_controller, c.customer you also need include c.name into GROUP BY, then use SUM(i.unall_amount) and SUM() around each CASE statement:
Code Snippet
Select c.credit_controller,c.customer,c.name, SUM(i.unall_amount),
SUM( CASE /*cash due*/
when i.kind='CSH' then i.unall_amount
else 0
end) as amtcash,
SUM( CASE /*sales_item.due_date - today < 0 */
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate()) < 0)
THEN i.unall_amount
ELSE 0
END )AS amtcur,
SUM( CASE /*sales_item.due_date - today > 0 & <= 30*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 0 AND 30) THEN i.unall_amount
ELSE 0
END )AS amt30,
SUM( CASE /*sales_item.due_date - today > 30 & <= 60*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 31 AND 60) THEN i.unall_amount
ELSE 0
END )AS amt60,
SUM( CASE /*sales_item.due_date - today > 60 & <= 90*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 61 AND 90) THEN i.unall_amount
ELSE 0
END )AS amt90,
SUM( CASE /*sales_item.due_date - today > 90*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
> 90) THEN i.unall_amount
ELSE 0
END ) AS amt120
From scheme.slcustm c INNER JOIN scheme.slitemm i ON c.customer=i.customer
GROUP BY c.credit_controller,c.customer,c.name
|||Hi Konstantin,
Thanks for your replay. It works.
I understand "When you use GROUP BY statetment, then any column used in select list must be also GROUP BY list or under some aggregation function." But I only want to group value by two columns - c.credit_controller and c.customer, not c.name and c.analysis_codes. When using "GROUP BY c.credit_controller,c.customer,c.name,c.analysis_codes", how it groups columns? does it group the columns one by one for all the four columns in the list in the GROUP BY clause? does it aggregate values for everty possible combination of the columns in the GROUP BY clause?
Though it seems it doesn't group that way when testing. But why?
Thanks,
yabing
|||
If you use "GROUP BY c.credit_controller,c.customer,c.name,c.analysis_codes" its group and aggregate for every possible combinations. So if you have few different values of c.name,c.analysis_codes for each unique pair of c.credit_controller,c.customer you will get additional lines.
But for example if you have following values:
c.credit_controller c.customer c.name
1 1 A
1 1 B
and use GROUP BY c.credit_controller,c.customer plus c.name in SELECT list. What do you want see in your result set? A or B?
You must include c.name in aggregation. For example you could use MIN or MAX or (if you have SQL Server 2005) write you own User-Defined Aggregate.This UDAG could concat values or do something else.
|||Dear Konstantin.
I have one question of GROUP BY,
When I use GROUP BY and use SUM around each CASE statement, it take very long query time.
Can we set a condition eg, query for c.name='A' only?
Thanks
pps1
|||You could write WHERE for filtering. GROUP BY works after WHERE