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.
| Author |
Topic |
|
werhardt
Constraint Violating Yak Guru
270 Posts |
 Posted - 2009-04-13 : 14:28:21
|
| I am having a problem with by formula below. What I am trying to do is create a formula that Groups by state but sums all the amounts up together. I thought what I did below was going to work but it came out with 3 Alabama and the totals individually.Can someone help me with this?Select Distinct [Provider State],Count([Claim Number]),SUM(CAST([Total Charges] AS MONEY)) AS TotalCharges,SUM(CAST([Savings] AS MONEY)) AS TotalSavings, SUM(CAST([Negotiated Allowed Charges]AS MONEY)) AS [Negotiated Allowed Charges]FROM IMPACT_PROD.DBO.VW_Chain_Claims_Discounted_and_Non_Discounted_ClaimsGroup by [Provider State], [Claim Number], [Total Charges],[Negotiated Allowed Charges]WHERE (YEAR([Out Date]) = YEAR(GETDATE()))Order by [Provider State] |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-04-13 : 14:30:32
|
| Just try to group by [Provider State] alone |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-13 : 14:32:23
|
| [code]Select [Provider State],Count([Claim Number]),SUM(CAST([Total Charges] AS MONEY)) AS TotalCharges,SUM(CAST([Savings] AS MONEY)) AS TotalSavings, SUM(CAST([Negotiated Allowed Charges]AS MONEY)) AS [Negotiated Allowed Charges]FROM IMPACT_PROD.DBO.VW_Chain_Claims_Discounted_and_Non_Discounted_ClaimsWHERE (YEAR([Out Date]) = YEAR(GETDATE()))Group by [Provider State]Order by [Provider State][/code] |
|
|
|
werhardt
Constraint Violating Yak Guru
270 Posts |
Posted - 2009-04-13 : 14:33:00
|
That worked, for some reason I thought you always had to have everthing in the select statement in the group by.Thanks!quote:
Originally posted by vijayisonly
Just try to group by [Provider State] alone
|
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-04-13 : 14:40:05
|
 np..you only need to group the fields that are not used in any aggregate fn. |
|
|
|
|
|
|
|
|
Group By Summary