Help with Group by and Percentage Sum

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 2008 Forums

Transact-SQL (2008)

Help with Group by and Percentage Sum

Author

Topic

mimuk
Starting Member

19 Posts

Posted - 2013-06-10 : 08:52:29

I have a query below that returns all sales items.

SELECT
,[comp_c_oglreference]
,[pgroup]
,[lineval]
FROM [CRM].[dbo].[vProductGroupList]

I need to group by pgroup and comp_c_oglreference and sum the lineval (item sales values) then display the percentage of the pgroups based on the total lineval

i.e.
comp_c_oglreference group percentage of sales
ELLI01 CSS 25
ELLI01 ELE 75
BAYA01 CDR 10
BAYA01 ELE 90

Original Data is something like this
comp_c_oglreference pgroup lineval
ELLI01 CSS 48.5
ELLI01 CDR 46.24
ELLI01 CPU 433.06
ELLI01 DUC 35.74

I hope this makes sense.

Cheers,
Mim

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-10 : 09:06:28

I didn't quite understand your sample data - i.e., how you got the results from the original data. Based on your description rather than the sample data, this may be what you need:

SELECT
	[comp_c_oglreference]
	,[pgroup]
	,SUM([lineval]) AS GroupSum,
	100.0*SUM([lineval])/SUM(SUM([lineval])) OVER() AS Percentage
FROM
	[CRM].[dbo].[vProductGroupList]
GROUP BY
	[comp_c_oglreference]
	,[pgroup]

mimuk
Starting Member

19 Posts

Posted - 2013-06-10 : 09:23:05

That is spot one James...
Thank you very much. I really appreciate your speedy reply.

Mim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources