Aggregates On Aggregates
Jun 13, 2000
(MSSQLS7) I have a table with fields similar to the
following:
StudentID
CourseID
CourseArea
EarnedHours
DateTaken
I need to create a report that totals all EarnedHours broken
down by category for a student for a date range. However,
courses can be taken multiple times, but should not count
more than once during the date range (the highest EarnedHours
should be used in the report preferably).
Currently, I've approached this with an SP that creates
a #temp table with CourseID, CourseArea, and MAX(EarnedHours)
GROUP BY CourseID for the date range and student specified,
then I'm selecting SUM(EarnedHours) GROUP BY CourseArea from
that.
Somehow, my current solution seems inelegant, but I haven't
been able to figure out a good way around it. It seems a real
waste to create a temp table, especially since this is a
high-use SP and the actual average subset of records involved
is really low (under 50). I'm probably missing something I
should already know... any ideas?
View 2 Replies
ADVERTISEMENT
Dec 4, 2001
Hi,
I have a SQL issue which I know can be solved ( reasonably simply ) but I can't seem to figure it out ( sometimes hard to think in sets ). Basically I have a table:
Report_ID Version date_created created_by
----------- ----------- --------------------------- -----------
17 1 2001-02-21 00:00:00.000 1
17 2 2001-02-22 00:00:00.000 4
17 3 2001-02-24 00:00:00.000 7
12 1 1999-12-01 00:00:00.000 7
12 2 1999-12-03 00:00:00.000 9
what I want to do is pick out all the details of the most recent recent version of each report_ID. i.e.
Report_ID Version date_created created_by
----------- ----------- --------------------------- -----------
17 3 2001-02-24 00:00:00.000 7
12 2 1999-12-03 00:00:00.000 9
I can get a query to return distinct report_ID's with most recent versions, but not all the details.
Any help is appreciated.
Tony.
View 1 Replies
View Related
Mar 8, 2007
Here is the code I have:
=Sum(IIF(sum( Fields!Total_Amount.Value, "Collat_Acct_Group2") < 0, 1,0))
I am trying to do a count but only if the sum of a value is greater/less than 0.
The error I get is:
The value expression for the textbox 'textbox146' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.
Please help .
Thanks
Elias
View 3 Replies
View Related
Sep 10, 2007
Hello:
I've been researching a likely common problem with reporting services: the inability to get an aggregate of an aggregate. One particular discussion thread comes close to solving my specific problem:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2021871&SiteID=1
Here's my problem. I have a table that groups data per month based on Count(). I'd like to get the max(count()) -- i.e., which month has the highest count?
For example:
JAN 30
FEB 20
MAR 25
I'd like to identify the month that has the max count. In this case, I'd like to capture the aggregate value "30" as being the max value of the three months displayed.
My goal is to embed a horizontal stacked-bar chart into the table (to the left of the count() values). Various types of medical services are being counted per month: Inpatient Stay, Outpatient Service, PCP visit. The key to displaying the horizontal stacked-bar charts is to make sure the maximum value of the chart is the same for all charts -- i.e., I need to know which month has the highest count and then set that value as the max limit on the chart.
Robert Bruckner's technical article on "Get More out of SQL Server Reporting Services Charts" briefly touches on the topic of embedded charts in a table, but doesn't go into the level of detail I'm considering.
I've also come across related information from SSW Rules to Better Reporting Services. Similar to Robert's article, SSW doesn't address scaling an in-line chart based on data that is aggregated, but rather scaling the in-line chart based on the values found in a specified data field.
Ultimately, I'd like to create an in-line bar chart that appropriate shows the month of JAN as having the longest horizontal bar, and FEB/MAR having appropriately scaled smaller bars.
Thanks in advance!
--Pete
View 5 Replies
View Related
Sep 20, 2007
So I already no this can't be done... but I need a suitable alternative (if one exists) so I don't have to competely re-write this already too huge query.
Anyways, in my select I have something like this:
sum( case when code in (1,2,3,4) then 0 else 1 end ) as total
which has now increase from four static values to a more dynamic format, that being a table with 47 values which may shrink or grow over time. Attempting the following fails:
sum( case when code in (select code_id from ExcludedCodes) then 0 else 1 end ) as total
because apparently you can't use selects or aggregates within an aggregate.
So any ideas on how I can get this working... is there no Array or List type variable I could just substitute in? I've already tried using a Table Variable, but that failed as well.
Please keep in mind, this is one line of a very large select containing many other fields and aggregates (on a fair amount of joins) which is used in at least four differerent reporting queries. If there is no quick and easy replacement trick I can do just let me know so I can start re-writing all of them (which is seriously going to make my head hurt).
View 4 Replies
View Related
Feb 20, 2007
I have data that looks like this
create table #TestData
(clt_num int,
proc_cde varchar(10))
insert #TestData (clt_num,proc_cde)
select 1000, 'H0017' union all
select 2000, 'T1016' union all
select 2000, 'H0036' union all
select 2000, 'T0017' union all
select 3000, '99999' union all
select 3000, 'AAAAA' union all
select 3000, 'H0039'
select 4000, '99999' union all
select 5000, 'H0017' union all
select 5000, 'H0066' union all
select 100, 'H0036;TT' union all
select 200, 'T1016;XX' union all
select 200, 'H0036' union all
select 300, '99999;HH' union all
select 300, 'AAAAA' union all
select 400, '99999' union all
select 500, 'H0017;15' union all
select 500, 'H0036;XX'
I want to select records for a given clt_num based on weights (precedence) given to the different proc_cde(s). The rules for selecting the rows are:
If clt_num only has one row select that row
Select row that has the highest proc_cde by precedence for given clt_num
If clt_num has two or more rows but no proc_cde with an assigned precedence select both rows.
Note that there is a twist with the proc_cds in that they can have garbage at the end in the data records and I don't care about the garbage H0036, H1036;XX will have the same weight.
Therefore my expected results are:
clt_numproc_cde
100H0036;TT
200T1016;XX
30099999;HH
300AAAAA
40099999
500H0036;XX
1000H0017
2000T0017
300099999
3000AAAAA
400099999
5000H0039
In attempting to solve this problem I created a temp table called ProcCde_Weights:
create table #ProcCde_Weights
(proc_cde varchar(10),
weight int)
insert #ProcCde_Weights
(proc_cde,weight)
select 'H0039', 10 union all
select 'T1017', 20 union all
select 'T1016', 30 union all
select 'H0036', 40 union all
select 'T2011', 50 union all
select '90806', 60 union all
select '90862', 70 union all
select 'T1002', 80 union all
select 'H2031', 90 union all
select 'H2023', 100
And wrote this query:
select t.clt_num,
t.proc_cde,
case when p.weight is null then 1000
else p.weight
end as weight
from #TestData t left join #ProcCde_Weights p on t.clt_num = t.clt_num
and p.proc_cde = left(t.proc_cde,5)
which gives me this:
clt_numproc_cdeweight
1000H00171000
2000T101630
2000H003640
2000T00171000
3000999991000
3000AAAAA1000
4000999991000
5000H00171000
5000H00661000
100H0017;TT1000
200T101630
200H003640
30099999;HH1000
300AAAAA1000
400999991000
500H0017;151000
500H0036;XX40
… but now I need to modify the query with using the min function or max function (I think) to only select rows according to the rules I gave.
… and once again I seem to have a mental block when it comes to composing queries with aggregate functions.
Thanks in advance for any help.
Laurie
p.s. can anybody point me to any books or tutorials that can help me wrap my mind around aggregate functions.
View 4 Replies
View Related
Apr 4, 2008
Hi, I have the following calculation in Textbox6
=round(sum((Fields!bill_hrs.Value)+(Fields!sales_hrs.Value)+(Fields!proj_hrs.Value))/24,0)
I need to avg the groups in the footer, but of course I cannot nest aggregates, so I tried this, but it doesn't work either. Any suggestions???
=Avg(ReportItems("Textbox6").Value)
=(Avg(ReportItems!Textbox6.Value))
View 1 Replies
View Related
Oct 26, 2004
Hi
I have similar problem and i explained in detail here http://www.balainfo.com/forums/viewtopic.php?t=39
Pls help me to fix this sql
Regds
Bala
View 1 Replies
View Related
Jan 16, 2004
I want to create a query to summarize sales for each of the last five weeks and group by product type ie.
select product, sum(amount this week), sum of (amount last week) ... group by product
my sales table looks like this
product date amount
x d1 xx
x d2 xx
y d3 xx
y d4 xx
x d5 xx
How can this be done?
thanks
View 4 Replies
View Related
Feb 13, 2007
Hi,
I have the following test data:
create table #TestData
(clt_num int,
ins_num int)
Insert into #TestData
(clt_num, ins_num)
Select 16, 1 union all
Select 16, 90 union all
Select 16, 999999 union all
Select 16, 389 union all
Select 18, 1 union all
Select 18, 90 union all
Select 18, 999999 union all
Select 24, 999999 union all
Select 24, 1 union all
Select 31, 1 union all
Select 31, 999999 union all
Select 31, 90 union all
Select 31, 389 union all
Select 35, 999999 union all
Select 35, 389 union all
Select 283, 1 union all
Select 283, 90 union all
Select 283, 999999 union all
Select 283, 310 union all
Select 500, 1 union all
Select 100, 90
… which I then combine the ins_num into insurance types:
select clt_num,
case
when ins_num = 1 then 'Caid'
when ins_num = 90 then 'Care'
when ins_num in (189,195,310) then 'HMO'
when ins_num between 381 and 389 then 'TPO'
when ins_num = 999999 then 'TPO'
end as InsuranceType
from #TestData
order by clt_num
… and get the following results:
clt_numInsuranceType
16Caid
16Care
16TPO
16TPO
18Caid
18Care
18TPO
24TPO
24Caid
31Caid
31TPO
31Care
31TPO
35TPO
35TPO
100Care
283Caid
283Care
283TPO
283HMO
500Caid
But what I really want is only one line per clt_num and where if clients have more than 1 insurance type the insurance type should be replaced with dual:
clt_numInsuranceType
16Dual
18Dual
24Dual
31Dual
35TPO or Dual (?*)
100Care
283Dual
500Caid
?* This is a special case and I do not know if the results should say Dual or not. I think TPO stands for Third Party Only (in which case they wouldn't care how many third parties the client has and the results should be TPO) … but if I am wrong then this should be Dual. I will ask my boss tomorrow to clarify, but it would be nice to have a solution for both ways.
I know I need to do something with count and stuff, but for some reason I have a mental block when it comes to agregate functions.
Thanks in advance for any help finishing up the query.
Laurie
View 5 Replies
View Related
Jun 23, 2005
I am having a similar issue in a Matrix report. The following is a message from someone else having the same problem.
View 8 Replies
View Related
Jan 16, 2008
I'd like to merge the 2 statements shown below into one. I'm wondering if there is a method of using PIVOT to get the SUM and COUNT aggregates in one statement. The only option I can get working is to use these as sub-queries but I'm hoping there is a better approach.
An answer would be great as would a better on-line resource than the BOL "Using PIVOT and UNPIVOT" topic.
Any guidance much appreciated.
/********
Get account type totals
********/
SELECT PVT.ACCOUNT_MANAGER_OID,
ISNULL(PVT.[CUSTOMER], 0) AS 'CUSTOMERS',
ISNULL(PVT.[OTHER], 0) AS 'OTHERS'
FROM ( SELECT A.OID, A.ACCOUNT_MANAGER_OID,
1 AS 'REVIEW_IND',
CASE WHEN A.TYPE = ( 'Customer' )
THEN TYPE
ELSE 'OTHER'
END AS TYPE
FROM ACCOUNTS A LEFT OUTER JOIN
( SELECT ACCOUNT_OID,
1 AS [REVIEW_IND]
FROM dbo.ACCOUNT_HISTORY
WHERE TABLE_NAME = 'ACCOUNTS' AND
FIELD_NAME = 'REVIEW DATE'
) AS DRV_R ON DRV_R.ACCOUNT_OID = A.OID
WHERE A.ACCOUNT_MANAGER_OID IS NOT NULL
) A PIVOT ( COUNT(A.OID) FOR TYPE IN ( [CUSTOMER], [OTHER] ) ) AS PVT
ORDER BY PVT.ACCOUNT_MANAGER_OID
/**********
Get accounts review totals
***********/
SELECT PVT.ACCOUNT_MANAGER_OID,
ISNULL(PVT.[CUSTOMER], 0) AS 'CUSTOMERS_REVIEWED',
ISNULL(PVT.[OTHER], 0) AS 'OTHERS_REVIEWED'
FROM ( SELECT A.OID, A.ACCOUNT_MANAGER_OID,
1 AS 'REVIEW_IND',
CASE WHEN A.TYPE = 'Customer' THEN TYPE
ELSE 'OTHER'
END AS TYPE
FROM ACCOUNTS A LEFT OUTER JOIN
( SELECT ACCOUNT_OID,
1 AS [REVIEW_IND]
FROM dbo.ACCOUNT_HISTORY
WHERE TABLE_NAME = 'ACCOUNTS' AND
FIELD_NAME = 'REVIEW DATE'
) AS DRV_R ON DRV_R.ACCOUNT_OID = A.OID
WHERE A.ACCOUNT_MANAGER_OID IS NOT NULL
) A PIVOT ( COUNT(A.OID) FOR TYPE IN ( [CUSTOMER], [OTHER] ) ) AS PVT
ORDER BY PVT.ACCOUNT_MANAGER_OID
View 5 Replies
View Related
Jun 21, 2007
I am trying to group on the number of distict field values. Basically:
=Ceiling(CountDistinct(Fields!ClientID.Value, Nothing)/10)
So for every 10 different clients, I get a group. I get an error with the above function saying aggregates are not allowed in a grouping expression. I tried creating a text box with the running value:
=RunningValue(Fields!ClientID.Value,CountDistinct,Nothing)
The value of the text box is correct, but I can't reference that text box in the grouping function. It says the textbox is undefined.
Any suggestions for a workaraound?
Thanks.
View 5 Replies
View Related
Jun 28, 2007
Greetings,
I could not figure out an easy way to Calc a group Avg in the group footer of the table.
Lets say I have UserID,GradeID,Score :
I create a group for GradeID and in the gradeid footer I use Avg(Score). Is there a way to tell RS you want the avg only for the group in scope?
View 1 Replies
View Related
Nov 12, 2007
I have a report that consists of three nested group levels. Each level can be expanded/collapsed. I wanted to have at each level the summed values:
e.g.
+ Group Header 1 Sum1 Sum2 etc..
+ Group Header 2 Sum1 Sum2 etc..
+ Group Header 3 Sum1 Sum2 etc..
Rather I have had to output the aggregated values like so ..
+ Group Header 1
+ Group Header 2
+ Group Header 3
Total Group Footer Sum1 Sum2 etc..
Total Group Footer Sum1 Sum2 etc..
Total Group Footer Sum1 Sum2 etc..
Is there a way to display the aggregated values at the header level of the grouping. I thought this could be possible if I could hide the Group Footer and directly reference the footer sum total values in the header. Any help would be greatly appreciated.
Theo
View 1 Replies
View Related
Mar 25, 2008
I have to write an aggregate which accumulates values in a relation to a parameter. Therefore I tried to create an aggregate with an additional input parameter.
While creating the aggregate, I get the following error message:
CREATE AGGREGATE failed because type 'MKT' does not conform to UDAGG specification due to method 'Accumulate'.
Does anybody know, how I could solve this?
Public Sub Accumulate(ByVal value As SQLDouble, ByVal param1 as SQLDouble)
Const uGK as double = 0.008
result = result + Math.Exp(-param1/(uGK *value) )
End Sub
View 7 Replies
View Related
May 21, 2008
Hi,
I would like to change the aggregate "sum" for "avg" for some of the measures of my cube.
I know it's possible with calculated member (i have already done that) but i would like a more efficient method, in fact i would like the results to be stored in my cube... is that possible ?
Thanks for your help
Regars
Francois
View 2 Replies
View Related
Jan 15, 2008
How would you get all those aggregates from the second SELECT statement worked into the first SELECT statement? Can this stuff even all be put into one statement?
What I'm trying to end up with is a table listing the Professor, Course, Year, Registration, and then the amount of A's, B's, etc.
What I have is a table listing the Professor, Course, Year, and Registration. I can also get a list with the counts for each grade. But I need to get the two together somehow. Any thoughts?
SELECT (gp.last_name + ', ' + gp.first_name) AS 'Prof',
gs.course,
LEFT(gs.quarter_year,4) AS 'Year',
COUNT(gs.enroll_id) AS 'Reg'
FROM section s INNER JOIN person p
ON p.person_id = s.person_id
INNER JOIN grade_sheets gs ON gs.section_id = s.section_id
WHERE s.quarter_year = 20073
GROUP BY p.last_name, p.first_name, s.course, s.quarter_year
ORDER BY 'Prof'
SELECT
Count_A =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'A' AND gs.section_id = @sectionID),
Count_B =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'B' AND gs.section_id = @sectionID),
Count_C =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'C' AND gs.section_id = @sectionID),
Count_D =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'D' AND gs.section_id = @sectionID),
Count_F =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'F' AND gs.section_id = @sectionID)
View 6 Replies
View Related
Jul 23, 2005
Are there any conditional aggregate functions, such as SUM()?An example would probably be the best way to describe what I'mtrying to do...I have the following table, named Orders, with the following records:ItemNo qty_ord paid------ ----------- ------T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 0.00T101B 1 0.00T101B 1 0.00T101B 1 0.00Z200L 1 50.00Z200L 2 100.00I want to produce the following result set:ItemNo qty_gross qty_net------ ---------- -------T101B 11 7Z200L 3 3The "qty_gross" column in the result set is the sum oftotal items ordered within the ItemNo grouping.Easy enough. However, I also want a column "qty_net" thatis the sum of qty_ord but ONLY IF the amount in the"paid" column is > 0.I tried using the HAVING clause, but that produces acatch 22 situation. If I say "HAVING paid > 0" thenthe qty_gross column is wrong because it leaves out rowsthat contain records with paid = 0 values. If I leaveout the HAVING clause, then the "qty_net" is wrong.Any ideas?select ItemNo, Sum(qty_ord) as qty_gross, Sum(qty_ord) as qty_netfrom Ordersgroup by qty_ord, paid, ItemNohaving paid > 0 ?????Thanks,Robbie
View 1 Replies
View Related
Oct 12, 2006
Hi all!
I have created (in CLR) a user defined aggregate. The scan order of this aggregate is important, because it return different results for different orders.
When i use it with a single group (using order by and where) is working fine.
For example
select id, dbo.cmp(myclolumn) as myres from (select top 100 percent * from mytable order by id,clmdate) where id=10 group by id
This works correctly. Now lets expand it by removing where id=10 clause
select id, dbo.cmp(myclolumn) as myres from (select top 100 percent * from mytable order by id,clmdate) group by id
I get slightly different results from what the right result must be.
Any idea?
View 5 Replies
View Related
Apr 10, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I am having some questions on indexed views and aggregate tables.
My question is: To improve the performance of the queries, is it better to use indexted views or aggregates tables for those aggregates which are often queried?
I am looking forward to hearing from you.
Thank you very much in advance for your help.
With best regards,
Yours sincerely,
View 6 Replies
View Related
