Group by Calculated Column - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Group by Calculated Column

Author

Topic

jj72uk
Starting Member

2 Posts

Posted - 2012-05-18 : 04:58:36

Hi All,

T-SQL noob here...

Having some trouble grouping by a calculated col. and was wondering if someone could point me in the right direction.

My current SQL code is:

SELECT
	   (x.[Partner] + ' ' + x.[Product]) AS [Brand],
	   x.[RetentionCalls],
	   x.[RenewalCalls],
	   x.[SR],
	   x.[Retained],
	   ([Retained]+[SR])/([RetentionCalls]+[RenewalCalls])*100.00 AS [C2S], --Need Percentage
	   ROUND(([RenewalCalls])/([RetentionCalls]+[RenewalCalls]),2)*100.00 AS [RCP] --Need Percentage
FROM
	(SELECT PartnerGroups.RetentionMIGroupings AS [Partner],
		   StoreMaster.Product,  
		   SUM(CASE WHEN ISNULL(StoreMaster.CallType,0)='Retention Call' THEN 1 ELSE 0 END) AS [RetentionCalls],
		   SUM(CASE WHEN ISNULL(StoreMaster.CallType,0)='Renewal Call' THEN 1 ELSE 0 END) AS [RenewalCalls],
		   SUM(CASE WHEN ISNULL(StoreMaster.[Type],0)='Standard Renewal' THEN 1 ELSE 0 END) AS [SR],
		   SUM(CASE WHEN ISNULL(StoreMaster.Retained,0)='Yes' THEN 1 ELSE 0 END) AS Retained  
	FROM StoreMaster INNER JOIN PartnerGroups
	ON
	StoreMaster.[Partner]= PartnerGroups.[Partner]
		
	GROUP BY StoreMaster.RACAF,
			 StoreMaster.[Date] ,
			 StoreMaster.Product,
			 PartnerGroups.RetentionMIGroupings
	) x

GROUP BY         x.[Partner],
		 x.[Product],
		 x.[RetentionCalls],
		 x.[RenewalCalls],
		 x.[SR],
		 x.[Retained]

I am wanting to Group By my calculated column [Brand], I have tried a few things with no luck.

Thanks,

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-18 : 05:50:20

Well, the grouping is done like this:


GROUP BY         (x.[Partner] + ' ' + x.[Product]),
                 x.[Partner],
		 x.[Product],
		 x.[RetentionCalls],
		 x.[RenewalCalls],
		 x.[SR],
		 x.[Retained]

But to get the actual groups you also need to use aggregation functions on the calculated columns [C2S] and [RPC].

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

jj72uk
Starting Member

2 Posts

Posted - 2012-05-18 : 07:54:31

Hi,

Thanks for the reply...

I unfortunately do not understand the usage, let alone heard of aggregation functions!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-18 : 10:19:38

You are using some already! SUM is an aggregate function. Also MAX, MIN, AVG...

Check Books Online for a full description: http://msdn.microsoft.com/en-us/library/aa255811(SQL.80).aspx

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources