May 22, 2007 at 11:11 am
Looking for a SQL statement to INSERT the following table data into another TARGET table:
Consultant Model Occurrences
---------- ----- -----------
Adam 55 3
Adam 56 12
Adam 57 10
Adam 58 8
Adam 59 4
Benny 56 11
Benny 58 8
Benny 59 6
Tom 55 1
Tom 56 2
Tom 59 33
(NOTE: only 5 Models exist: 55-59)
The TARGET table's column names & data should look like this:
Consultant Model 55 Model 56 Model 57 Model 58 Model 59 Total Occurrences
---------- -------- -------- -------- -------- -------- -----------------
Adam 3 12 10 8 4 37
Benny 0 11 0 8 6 25
Tom 1 2 0 0 33 36
May 22, 2007 at 11:29 am
insert can just be prepended to a select. You need to make sure you're getting the right data.
select [Consultant],
sum(case when [Model]=55 then 1 else 0 end) "Model 55",
sum(case when [Model]=56 then 1 else 0 end) "Model 56",
sum(case when [Model]=57 then 1 else 0 end) "Model 57",
sum(case when [Model]=58 then 1 else 0 end) "Model 58",
sum(case when [Model]=59 then 1 else 0 end) "Model 59"
from source
group by [Consultant]
May 22, 2007 at 11:37 am
Since you're posting in the SQL Server 2005 forum you should also consider using PIVOT to do this.
May 22, 2007 at 1:40 pm
Thx Oblio - that worked great. Would you happen to know how to resolve this issue? I'm receiving an error in Management Studio because I'm performing a COUNT(*) inside of a SUM(CASE... statement.
Msg 130, Level 15, State 1, Line 5
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
SELECT LTRIM(CLSTLastName) + ', ' + LTRIM(CLSTFirstName) AS Consultant,
SUM(CASE WHEN Value=55 THEN (Perf_YTD - BB_Return_YTD) ELSE 0 end) / COUNT(*) "Conservative",
SUM(CASE WHEN Value=56 THEN (Perf_YTD - BB_Return_YTD) ELSE 0 end) / COUNT(*) "Moderately Conservative",
SUM(CASE WHEN Value=57 THEN (Perf_YTD - BB_Return_YTD) ELSE 0 end) / COUNT(*) "Moderate",
SUM(CASE WHEN Value=58 THEN (Perf_YTD - BB_Return_YTD) ELSE 0 end) / COUNT(*) "Moderately Aggressive",
SUM(CASE WHEN Value=59 THEN (Perf_YTD - BB_Return_YTD) ELSE 0 end) / COUNT(*) "Aggressive",
SUM(CASE WHEN Value Between 55 and 59 then 1 else 0 end) "Grand Total"
FROM DBAdmin..RGPerfExtract
GROUP BY LTRIM(CLSTLastName) + ', ' + LTRIM(CLSTFirstName)
ORDER BY LTRIM(CLSTLastName) + ', ' + LTRIM(CLSTFirstName
May 22, 2007 at 1:49 pm
try
avg(SUM(CASE WHEN Value=55 THEN (Perf_YTD - BB_Return_YTD) ELSE 0 end))?
Also, for "grand total"s, try GROUP BY ... WITH ROLLUP
May 22, 2007 at 1:58 pm
Unfortunately, I am still receiving the same error msg:
Msg 130, Level 15, State 1, Line 3
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
SQL:
SELECT LTRIM(CLSTLastName) + ', ' + LTRIM(CLSTFirstName) AS Consultant,
AVG(SUM(CASE WHEN Value=56 THEN (Perf_YTD - BB_Return_YTD) ELSE 0 end)) "Moderately Conservative"
FROM DBAdmin..RGPerfExtract
GROUP BY LTRIM(CLSTLastName) + ', ' + LTRIM(CLSTFirstName)
ORDER BY LTRIM(CLSTLastName) + ', ' + LTRIM(CLSTFirstName)
May 22, 2007 at 2:04 pm
Can you post a ddl & sample data?
May 22, 2007 at 2:21 pm
DDL
---
CLSTLastName VARCHAR(40)
CLSTFirstName VARCHAR(40)
Value VARCHAR(100) --< don't blame me: I didn't design !!!
Perf_YTD DECIMAL(18,4)
BB_Return_YTD DECIMAL(18,4)
CLSTLastName CLSTFirstName Value Perf_YTD BB_Return_YTD
------------ ------------- ----- -------- -------------
Jones Adam 55 1.234 2.023
Jones Adam 56 2.235 -0.321
Jones Adam 57 0.034 3.232
Jones Adam 58 -0.989 6.444
Jones Adam 59 3.334 -1.822
Smith Benny 56 4.004 1.923
Smith Benny 58 2.031 2.176
Smith Benny 59 -0.003 1.332
Tyler Tom 55 0.002 0.012
Tyler Tom 56 0.121 0.554
Tyler Tom 59 3.322 0.838
May 23, 2007 at 6:53 am
Alright, try this:
DECLARE @data TABLE(
lname VARCHAR(40)
,fname VARCHAR(40)
,val INT
,perf DECIMAL(9,4)
,retrn DECIMAL(9,4)
)
INSERT @data VALUES('Jones','Adam',55,1.234,2.023)
INSERT @data VALUES('Jones','Adam',56,2.235,-0.321)
INSERT @data VALUES('Jones','Adam',57,0.034,3.232)
INSERT @data VALUES('Jones','Adam',58,-0.989,6.444)
INSERT @data VALUES('Jones','Adam',59,3.334,-1.822)
INSERT @data VALUES('Smith','Benny',56,4.004,1.923)
INSERT @data VALUES('Smith','Benny',58,2.031,2.176)
INSERT @data VALUES('Smith','Benny',59,-0.003,1.332)
INSERT @data VALUES('Tyler','Tom',55,0.002,0.012)
INSERT @data VALUES('Tyler','Tom',56,0.121,0.554)
INSERT @data VALUES('Tyler','Tom',59,3.322,0.838)
SELECT LTRIM([lname]) + ', ' + LTRIM([fname]) "Consultant"
,CASE WHEN [val]=55 THEN ([perf]-[retrn]) ELSE 0 END
,CASE WHEN [val]=56 THEN ([perf]-[retrn]) ELSE 0 END
,CASE WHEN [val]=57 THEN ([perf]-[retrn]) ELSE 0 END
,CASE WHEN [val]=58 THEN ([perf]-[retrn]) ELSE 0 END
,CASE WHEN [val]=59 THEN ([perf]-[retrn]) ELSE 0 END
FROM @data
SELECT LTRIM([lname]) + ', ' + LTRIM([fname]) "Consultant"
,AVG(CASE WHEN [val]=55 THEN ([perf]-[retrn]) END) "Conservative"
,AVG(CASE WHEN [val]=56 THEN ([perf]-[retrn]) END) "Moderately Conservative"
,AVG(CASE WHEN [val]=57 THEN ([perf]-[retrn]) END) "Moderate"
,AVG(CASE WHEN [val]=58 THEN ([perf]-[retrn]) END) "Moderately Aggressive"
,AVG(CASE WHEN [val]=59 THEN ([perf]-[retrn]) END) "Aggressive"
FROM @data
GROUP BY LTRIM([lname]) + ', ' + LTRIM([fname]) WITH ROLLUPBTW, This is calculating the average of *each person*, not against the entire group. Also, I've removed the "ELSE 0" -- if you leave that in, it'll attempt to average a zero in for each "value", and I think that will taint the results.
May 23, 2007 at 8:09 am
Oblio - your solution worked PERFECTLY !! I can't thank you enough.
- Bill (DBASkippack)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply