SQLTeam.com | Weblogs | Forums

Column in select not in group


#1

Respected Techie,

May someone please help me how to acheive this.

/*
sum of Net Product Sales for each salesperson_number in a sales_group for a given month
I am looking for all the column but group by only on sales_group
*/
Declare @table1 table
(
Month VARCHAR (5),
SALES_GROUP VARCHAR (20)
SALES_NUMBER NUMERIC (10,0)
[Net Product Sales] FLOAT
)

insert @table1

SELECT '8', 'E' , '9298', '14.89' UNION ALL
SELECT '8', 'E' , '9298', '99.3' UNION ALL
SELECT '8', 'E' , '9290', '24.29' UNION ALL
SELECT '8', 'E' , '9290', '58.78' UNION ALL
SELECT '7', 'E' , '9298', '138.66' UNION ALL
SELECT '7', 'E' , '9298', '286.81' UNION ALL
SELECT '7', 'E' , '9298', '125.29' UNION ALL
SELECT '7', 'E' , '9299', '76.91' UNION ALL
SELECT '7', 'E' , '9299', '90.5' UNION ALL
SELECT '7', 'E', '9299', '43.19'

Result


#2

Here you go:

SELECT	T.Month,
		T.SALES_GROUP,
		T.SALES_NUMBER,
		SUM(T.[Net Product Sales])
FROM	@table1 AS T 
GROUP	BY T.Month,
		T.SALES_GROUP,
		T.SALES_NUMBER
--Uncomment this for particular ordering
--ORDER	BY T.Month DESC;

#3

Thanks Doshan.
will it be possible if i want to add customer column and don't want to include it in group by .
and all customer in a group of salesperson have same total.
i don't think so, but whether any other alternative. please suggest.


#4

There is no customer column in your test data and that query matches your expected output. Do you have an example of this customer data and what the new expected would be?