Cannot Perform An Aggregate Function On An Expression Containing An Aggregate Or A Subquery.
Can any1 tell me why i am getting an error
SELECT DISTINCT
--p.voucher,
--p.amount,
p.siteID,
b.siteID,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers
FROM
BillingTotal b,
Payment p
--WHERE
-- s.sitename=@cmb1
--AND p.siteid = s.siteid
-- p.voucher = 0
-- p.voucher = 1
GROUP BY p.siteID,b.siteID
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
View Complete Forum Thread with Replies
Related Forum Messages:
Cannot Perform An Aggregate Function On An Express
SELECT((datediff("d", arrival,departure)) - ((select case when datediff("d", arrival, '07/24/07') < 0 then (select case when datediff("d", '07/27/07',departure) < 0 then 0 else datediff("d", '07/27/07',departure) end) else datediff("d", arrival, '07/24/07') end)) as nights does any one know how can i sum the nights right now the error is Server: Msg 130, Level 15, State 1, Line 4 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
View Replies !
Cannot Perform An Aggregate Function On An Express
as a follow on from my last post, here is my sql to give the sum of all payments we have recived for a particular batch of data , dependant upon the rate. Select SUM(Case dp.ReceivedByID When 2 Then dp.PaymentAmount * (cn.DirectRate / 100) When 1 Then dp.PaymentAmount * (cn.FeeRate / 100) Else dp.PaymentAmount * ((cn.FeeRate + cn.FieldRate) / 100) End) From dbo.DebtPayment dp, dbo.ImportBatchItem bi, dbo.Debt d, dbo.Contract cn where cn.ClientContractID=d.ContractID AND d.Debtid=dp.DebtID AND dp.DebtID=bi.ItemID AND bi.ImportBatchID=101 AND cn.ClientID=1021 cn.DirectRate is the current contract rate. This rate can be changed at any time on the system. i have created a table that stores the the rate for a particulat time frame. with the sql to return ruturn the rate for a time period, client & contract like so. select cfc.ComRate from dbo.mContractFeeChange cfc where cfc.ClientID=1021 and cfc.contractid=12 and someDate >= cfc.StartDate AND someDate <= cfc.EndDate when i plum this as a sub-query into my main query as so Select SUM(Case dp.ReceivedByID When 2 Then dp.PaymentAmount * ( (select cfc.ComRate from dbo.mContractFeeChange cfc where cfc.ClientID=1021 and cfc.contractid=12 and dp.PaymentDate >= cfc.StartDate AND dp.PaymentDate <=cfc.EndDate)/ 100) When 1 Then dp.PaymentAmount * (cn.FeeRate / 100) Else dp.PaymentAmount * ((cn.FeeRate + cn.FieldRate) / 100) End) From dbo.DebtPayment dp, dbo.ImportBatchItem bi, dbo.Debt d, dbo.Contract cn where cn.ClientContractID=d.ContractID AND d.Debtid=dp.DebtID AND dp.DebtID=bi.ItemID AND bi.ImportBatchID=101 AND cn.ClientID=1021 I get the following error. "Cannot perform an aggregate function on an expression containing an aggregate or a subquery" any help most welcome
View Replies !
Aggregate Function On SubQuery
I am working on a view in SQL Server 2005. I am trying to get a list of the number of sessions each user had by user. I tried doing it this way, but SELECT userid, MAX ((SELECT COUNT(DISTINCT sessionId) AS SESSIONCOUNT FROM dbo.Sessions AS OD HAVING (sessionId = O.sessionId))) AS MAXSESSION FROM dbo.Sessions AS O GROUP BY userid but it throws an error 'Cannot perform an aggregate function on an expression containing an aggregate or subquery.' Is there an elegant solution for this? Thanks,
View Replies !
SUM Of Report Field With Expression Which Has COUNT Aggregate Function
Hi everyone, I have created a report which has 3 groups. The report output as shown below. I am having trouble getting the SUM of Total Credtis for each Org. Can't seem to get the total 42 and 16 (highlighted), but can get total unists 11 and 13. I get expression contains aggregate function. This is because Units assessed is the Count of IDs (details hidden from the report). Report has three groups Org , Assessor and Unit. Can someone please help me with this? Appreciate help. Thank you, Ski Org 1(Group1) Unit Credits Units Assessed(# of Trainees) TotalCredits Assessor 1 Unit 1 2 4 (Count of Ids) 8 (2*4) Assessor 2 Unit 2 1 2 2 (1*2) Assessor 3 Unit 3 5 2 10 (5*2) Unit 4 2 1 2 Assessor 4 Unit 5 10 2 20 -------------------------------------------------------------------------------------------------------- 11 42 ----------------------------------------------------------------------------------------------------------- Org 2 Assessor 3 Unit 1 2 3 6 Assessor 4 Unit 6 1 10 10 -------------------------------------------------------------------------------------------------------- 13 16 --------------------------------------------------------------------------------------------------------
View Replies !
Aggregate On Subquery
I want to return only the sum total of each of the following two columns generated by this query, but when I wrap them in SUM() I get an error stating that I can't use an aggregate function on an aggregate or subquery. Is there another approach that I might take to sum these? SELECT CASE soitem.fmultiple WHEN 1 then (SELECT funetprice FROM sorels WHERE (sorels.fsono = shmast.fcsono) AND sorels.frelease = SUBSTRING(shitem.fsokey,10,3) AND sorels.fenumber = shitem.fenumber) * shitem.fshipqty ELSE (SELECT top 1 funetprice FROM sorels WHERE (sorels.fsono = shmast.fcsono) AND sorels.finumber = soitem.finumber) * shitem.fshipqty END as ExtPrice, CASE CAST((shitem.fshipqty) as int) % nullif(CAST(inmast.fnusrqty1 as int),0) WHEN 0 then (CAST((shitem.fshipqty) as int) / nullif(CAST(inmast.fnusrqty1 as int),0)) ELSE (CAST((shitem.fshipqty) as int) / nullif(CAST(inmast.fnusrqty1 as int),0)) + 1 END as BoxCount FROM shmast INNER JOIN shitem ON shmast.fshipno = shitem.fshipno INNER JOIN soitem ON (soitem.fsono = shmast.fcsono) AND (Convert(Int,soitem.finumber) = Convert(Int,SUBSTRING(shitem.fsokey,8,10)) / 1000) LEFT JOIN somast ON (shmast.fcsono = somast.fsono) LEFT JOIN inmast ON (soitem.fpartno = inmast.fpartno) WHERE (shmast.fbl_lading='00000000000000003784') AND (shitem.fshipqty > 0)
View Replies !
Pb To Retreive Max Value With Aggregate Subquery
I would like to know which products are my best sells by sellers, but iwould like to retreive this info by product id, seller id and the totalamount of sells for this product.My Sells table is :Seller_idProduct_idTotaldate_s1 2 1020/05/042 4 1512/05/043 5 2206/06/041 5 1807/06/044 8 1213/05/047 2 1119/05/043 4 1421/05/042 4 1418/05/041 5 1817/06/042 5 5008/05/04etc....I know how to retreive the total sells by product id and seller idSELECT Seller_id, Product_id, SUM(Total) AS totalFROM SellsWHERE date_s > '01/05/04'GROUP BY Seller_id,Product_id order by Seller_idSeller_idProduct_idTotal1 5 361 2 102 5 502 4 293 5 223 4 14I would like retreive only the max of total, and the Seller id andproduct id, like this :Seller_idProduct_idTotal1 5 362 5 503 5 22How can i do without using a temp table ?Thanks for your help.
View Replies !
Aggregate Function
Does anyone know how to make a query and use an aggregate function? This is my current code...any help would be great. "SELECT tblTopic.Topic_ID, tblTopic.Subject, MAX(tblThread.Message_date) AS MessageDate, tblThread.Message FROM (tblThread INNER JOIN tblTopic ON tblThread.Topic_ID = tblTopic.Topic_ID) WHERE (tblThread.Message_Date LIKE '%' + @fldGenus + '%' GROUP BY tblTopic.Topic_ID, tblTopic.Subject, tblThread.Message"> Also, How can i limit the query to only bringing up 5 records? I'm trying to get a datagrid to show the 5 most recent forum posts for a particular category. Thanks.
View Replies !
Aggregate Function
I have three tables, tblschedule, tblresource and tblemployeename. in tblschedule table there are scheduleID, resourceID and employeeID. In tblResource there are ResourceID and ResourceName. In tblemployeename there are EmployeeID, EmployeeFName and EmployeeLame. I want to have a report that show how many times the resource has been reserved by employee. i would like to have a report. Look like the following: ResourceName EmployeeFName EmployeeLName (Or use EmployeeName) Number of record. How to write a query? I use asp.net as front end. Thank for your help.
View Replies !
Aggregate Function MAX
Hi I have a query where I need to use an aggregate function MAX in where clause, I know that MAX cannot be used in a Where clause because it is an aggregate function. Can anyone help me out in writing this query? SELECT * FROM ACCOUNT_REVIEW AR INNER JOIN QUESTION_RESPONSE ON AR.Review_ID = QUESTION_RESPONSE.Review_ID WHERE (MAX(AR.Review_Date) IS NULL) I need it asap. Thanks in advance, K
View Replies !
Using Aggregate Function
I want to use the Aggregate function in Reporting services for a drilldown report. The field to be aggregated is a Percentage field. It is based on an analysis services datasource There are 3 groups for the drilldown Company -> Branch -> Employee The detail level has the individual sales The lowermost group 'Employee' shows the percentage sales for each employee The next level 'Branch ' shows the percenatge sales for all employees in that branch The uppermost level 'Company' shows the percentage sales for all branches in the company When I try to give the scope for the aggregate function at the 'Branch' level it fails Aggregate(Fields!TAT_Percent.Value,"grp_Employee") Aggregate(Fields!TAT_Percent.Value,"grp_Sales_Details") where grp_Employee is the name of the Employee grouping and grp_Sales_Details is the name of the details group I get the error "The scope parameter must be the name of a scope where all containing grouping scopes have group expressions which are simple field references" I have tried the aggregate function without scope and that too doesnt work. What am I doing wrong? .
View Replies !
Single Value From An Aggregate Function
Hey everyone, I'm looking for a way to insert a count of a set of records into a table and then read that table and produce an XML file from that dataset. The problem that I'm coming across is that when you do this type of query you have to specify one of the values from the table and then get a count for the number of records of that particular range of values. The table that I am trying to get the data from will look something like this: ItemID ModuleID Description ......1 2869 blah blah blah2 2869 blah blah blah3 2869 blah blah blah4 2690 bit bit bit5 2690 bit bit bit6 2690 bit bit bit So I count the 2869's and 2690's and place the ModuleID and the count of the ItemID's in another table. Then I need to extract the data from the table and put it into an XML file that is readable by the ReadXML method in ASP.NET. So far I've got the SPROC to do only one record at a time but I would like one that does the whole table rather than one at a time using a parameter.
View Replies !
Help In MAX Aggregate Function, Plz, Urgent
wat i want wat i need to get is all maxBids wich are more than the last bid made and i shuould get the maximumBid if the same user(uId in this case) has two entried of maxBids. and i don't want the last bidder(uId) to get into the result u can see it below, where i have showed the results. so i want 1) uId and maxBid::: with no redundancy, but should get largest maxBid offered by uId if more than one maxBid is offered by same uId , look at the table, i have marked it with X in bold 2) don't want to get the uId of last bidder. marked with BOLD Y situation is like i m trying to do proxy bidding(automated bidding). select temp1.uId,maxBid,bidDate from temp1 where temp1.auctionId = 7 and temp1.uId <> 2 I don't want last bidder, how to discard last entry, wat should be this query, how to get last bidder's uId and temp1.maxBid > = (select max(currBid) from temp1 where auctionId=7) order by bidDate heres the table bidIdauctionIduIdcurrBidmaxBidbidDate 17715500015500012/5/2003 3:31:51 AM 27816000018000012/5/2003 4:27:03 AM---X 37216500016500012/5/2003 5:37:29 AM 47817000019000012/5/2003 5:39:29 AM---X 57117500017500012/5/2003 5:44:29 AM---Y heres the result uIdmaxBid bidDate 8180000.00002003-12-05 04:27:03.000-----Z smaller bid of same uId 8190000.00002003-12-05 05:39:29.000 1175000.00002003-12-05 05:44:29.000-----Z latest/last bidder i have all userIds in t_user table. i don't want the rows with X
View Replies !
Use Of Subqueries In An Aggregate Function
hi there, I have a query that works on sybase and want to make it also works on SQL Server. The problem is that in this query I 'm using a subquery in an aggregate function. It seems that SQL Server unlike Sybase doesn't support the use of subquery in aggregate function. How can I overcome this problem. Thanks, Fady
View Replies !
How Would I Do This - Select With Aggregate Function
I have a table like this below: <PRE> Page Book Release MaxPages 1234ABC A1 1234ABC B2 9999ABC D1 9999ABC E2 7777ABC A1 7777ABC C2 </PRE> I want to select every page of the book, but only the highest release of that page. Something Like: Select Book, Page, Max(Release), MaxPages From Table But I can't quiet figure it out. Thanks for any help.
View Replies !
Aggregate Function Update
I am trying to write an update statement based on an aggregate and it will not let me. Please find below the SQL. update abtimesummary set hours = sum(a.hours) from abtimestore a join abtimesummary b on (cast(a.weekno as varchar(10)))+'-'+(cast(a.empno as varchar(10))) = b.summaryid and this is the error message: Server: Msg 157, Level 15, State 1, Line 2 An aggregate may not appear in the set list of an UPDATE statement. Can someone tell me how to get round this please? Many thanks
View Replies !
Aggregate Function Error
I m facing an issue while converting the code from Sybase to Sql Server .one particular query is giving the error like "Cannot perform an aggregate function on an expression containing an aggregate or a subquery". select FLS.lot_id, count((select min(rtrim(CMAPL.carrier_id)) from fab_lot_extraction..carrier_map_lot CMAPL where FLS.lot_id = CMAPL.lot_id)), FLS.hold_datetime from fab_lot_extraction..fab_lot_status FLS group by FLS.lot_id, FLS.hold_datetime please suggest any solution for this.
View Replies !
Aggregate Function With MSSQL 7.0
with sybase-sql it is possible to use a SELECT-statment like this: (select list(string(column_name, if "order"='A' then ' ASC' else ' DESC' endif)) from SYS.SYSIXCOL join SYS.SYSCOLUMN where index_id=SYSINDEX.index_id and SYSIXCOL.table_id=SYSINDEX.table_id) the result will be like: field1 asc, field2 asc, field3 desc..... How can i manage this with MSSQL 70 ? I havn't found any function working like this. Any suggestions ? thankx from Germany
View Replies !
Aggregate Function "aggrevating Me!!!"
Hello folks, I am trying to do a simple query on SQL 7.0 using sql query analyzer on a hugh db. Here is the query: select logid,count(logid) from tablename group by logid order by logid desc compute sum(count(logid)) The results should be a large number say 10471066...and what am I getting 104710 yeap the last two digits got truncated. Now I know that I should get 10471066 because I also queried with count(*) and count(logid) without compute and numbers are same. Interestingly when I use rollup it gives me the numbers I am expecting. select logid,count(logid) group by logid with rollup order by logid desc This mystery is driving me crazy. What is it that I am doing wrong with compute or am I missing something. Any and all help or hints are appreciated. Thanks in advance...I'll get a good night sleep else this problem will keep daunting me.... HP
View Replies !
Aggregate Function Trouble
Problem 1: Suppose I have a table T( A, B, C, D, E ) I want to retrieve D, E for all rows which have minimum C when we do a group by on A and B. The query would look something like SELECT D, E FROM T HAVING C = MIN( C ) GROUP BY A, B Unfortunately, SQL server gives the error that D, E are not in a group by clause. One way is: SELECT T.D, T.E FROM T, ( SELECT A, B, MIN( C ) AS F FROM T GROUP BY A, B ) AS T1 WHERE T.A = T1.A AND T.B = T1.B AND T.C = T1.F What I want to know is whether there is a simpler way of doing this or not, kinda like the first illegal query. Problem 2: Suppose in the query SELECT A, B, CASE WHEN C = 'A' THEN 1 WHEN C = 'B' THEN 2 END ORDER I want to do MIN( ORDER ). The simple way would be SELECT A, B, MIN( CASE WHEN C = 'A' THEN 1 WHEN C = 'B'' THEN 2 END ) But this gives an error. There is a way: SELECT T.A, T.B, MIN( T.C ) FROM ( SELECT A, B, CASE WHEN C = 'A' THEN 1 WHEN C = 'B'' THEN 2 END AS C ) AS T but I want to know if there is a simpler way to do this (kinda like the first illegal query). Regards, id
View Replies !
Custom Aggregate Function
hi how do i write a custom aggregate function or select query.I have Table name MemberDetail where which has two column Memberid and WokingDay.In this table same member could have multiple working days. i want select query that will return single row for each member and his/her quama separated working days. without using cursor. how do i do that. eg Table MemberDetail MemberID WorkingDay M1234 12 M1234 10 M1555 02 M1555 15
View Replies !
Boolean Aggregate Function
I don't know if this is the right forum. I have a MS ACCESS table like this: ID - Col1 - Col2 Number - Yes/No - Yes/No ===================== 1 - Yes - No 1 - No - No 2 - No - No 2 - Yes - No 2 - Yes - Yes I would like to create a view that groups BY ID and combines the boolean columns into 1 value using OR: ID - Col1 - Col2 ===================== 1 - Yes - No 2 - Yes - Yes Is there an aggregate function for this in T-SQL? If not, how can I solve this?
View Replies !
Sum Aggregate Function Problem
Hi, I've the following query: select F_Mode, Issue_Count, Sum(Issue_Count) as IssueSum, milestone from rpt_fact_Paretos Group By F_Mode having milestone = 'SDS' Sample Data: F_Mode Issue_Count milestone Fail1 5 SDS Fail2 6 SDS If the above query is executed, the resultset is: Fail1 5 5 SDS Fail2 6 6 SDS Question: I'm not getting the SUM of Issue_Count. I agree that I'm using F_Mode as Group By and hence the result. But if I use milestone as Group By I won't get all the F_Mode in the result set. I'm looking for a result like this: Fail1 5 11 SDS Fail2 6 11 SDS How can I achievce this? Thanks in advance, SD
View Replies !
Aggregate Function Error
I have made an aggregate function sql (as far as I understand).I need the sum but also the features column. But I can only read one column in the select.Here is the select trying to read two columns: select oitems.catalogid,oitems.features, sum(oitems.numitems) as SumOfItems from oitems here is the error: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'features' as part of an aggregate function. ...anyone?
View Replies !
Problem About 'aggregate Function' Please Help
Hi, I just started database class Today is the first day i'm writtng code in sql SO when i try to run this code SELECT CUSTOMER.CUS_CODE,CUS_BALANCE, SUM(LINE_UNITS*LINE_PRICE) AS TotalPurchases FROM CUSTOMER, INVOICE, LINE WHERE CUSTOMER.CUS_CODE=INVOICE.CUS_CODE AND INVOICE.INV_NUMBER=LINE.INV_NUMBER GROUP BY CUSTOMER.CUS_CODE; It says "You tried to execute quiry that does not include the specified expression 'CUS BALANCE' as a part of aggregate function. Please help me to fix it.
View Replies !
Product As Aggregate Function?
Hi guys, Does anybody know a convenient way to return the product of a column, similar to something like the 'sum' function? If I have a table of form id-date-value, I'd like to do something like: select id, date, product(val) If not, does anyone know of a way one could structure this as a subquery? If all else fails, I suppose I'll have to resort to a loop or cursor, but I'd rather not. Any help would be much appreciated.
View Replies !
Aggregate Function In SQL Statement.
Hello, I want to count how many occurences their is of each date that is returned by my sql query. I am not sure how to add the aggregate function code to my query I know how to just tell it to count all records, but not to tell it to count for each group of dates. For example I want it to count how many times 5/6/08 shows up in the returned results and so on. Here is my query I currently have. Any help would be greatly appreciated! Thanks! The enc_timestamp is my date field. Select a.template_id, a.enc_timestamp, a.created_by, b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname From template_audit a Join user_mstr b on a.created_by = b.user_id GROUP BY a.template_id, a.enc_timestamp, a.created_by,b.first_name, b.last_name Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241) ORDER BY a.enc_timestamp, b.first_name, b.last_name; Thanks in advance, Sherri
View Replies !
How To Pass Parameter In Max Aggregate Function
i have on table temp and it has column name fhID, now that column name and table name comes as parameter in stored procedure from c#. now stored procedure has code somthing like below @column nvarchar(50) @tbname nvarchar(50) -- both are parameter comes from c# decalre @maxid select @maxid=max(@column) from @tbname rather than prints the maximum values of "fhid" it prints column name itself. now i how can i achive actual working of max function using parameter. is there any other way to pass parameter in aggregate function or it is not allowed to pass.??? plz help me, thanks vishal parekh 'fhid' now how can
View Replies !
UPDATE Another Table Using Aggregate Function
Here is the example: I have two tables. One has Projects with the total amt of hours worked on the project itself. The other is an Employee_Projects table with individual rows of hrs per employee worked on the above referenced projects. I need to SUM all the hrs from the Employee_Projects table and GROUP BY project number, then UPDATE the Projects table with the sum of hours where the Project Number from table A matches the Project Number from table B. Of course, you cant use an aggregate function in an UPDATE clause, so what would be the easiest way to do this?? Any help would be much appreciated. -C
View Replies !
Update Using Group By And Aggregate Function
I'm trying to update a varchar field using SUM. I keep getting the error that the sub query returns more than one value. UPDATE CIRSUB_M SET TRM_DMO = SUBSTRING(TRM_DMO,1,11) + (SELECT CAST(SUM(COPIES) AS VARCHAR(5)) FROM CIRSUB_M WHERE BIL_ORG = '02' AND CRC_STS IN ('R','P','Q','T') GROUP BY PUB_CDE, DNR_NBR) WHERE BIL_ORG = '02' AND CRC_STS IN ('R','P','Q','T') Example PUB_CDE DNR_NBR COPIES TRM_DMO THN 000000092637 100 A THN 000000092637 200 B THN 000000082455 100 A THN 000000082455 200 B THN 000000051779 100 A Updated THN 000000092637 100 A300 THN 000000092637 200 B300 THN 000000082455 100 A300 THN 000000082455 200 B300 THN 000000051779 100 A100
View Replies !
Aggregate Function/Join Troubles
Hey, I am running into trouble performing a second join on a column while using aggregate functions over that table. What I think is happening is that the aggregate values (sum, avg, count) are being multiplied because I am joining on the same table a second time, but I don't know how to fix or avoid this problem. Here's the query: --- SELECT CAST(ro.xing_tc_no AS INT) AS loc_id, SUM(ro.total_fatal) AS tot_fatal, SUM(ro.total_serious) AS tot_serious, SUM(ro.total_minor) AS tot_minor, COUNT(ro.report_no) AS tot_accidents, AVG(ro.NO_OCCUPANTS) AS num_occupants FROM UserHotspots uh LEFT JOIN rods_k_crossing AS rkc ON rkc.location_id = uh.CrossingID LEFT JOIN rods_occurrence AS ro ON rkc.tc_location_nbr = ro.xing_tc_no GROUP BY ro.xing_tc_no --- The second join is also performed on rods_k_crossing and that is when the sums, avgs, and counts get multiplied. If I take that second join out the numbers come out fine. Thanks for any help!
View Replies !
Aggregate Function - Tough Query For Me...
I currently have the following query: Quote: select distinct a.memberFirstName, a.memberLastName, c.ChapterName, d.divisionName, count(f.memberID) as numMembers FROM Members a INNER JOIN groupLeaders b ON a.memberID = b.memberID Inner JOIN Chapters c ON c.chapterID = b.chapterID LEFT JOIN divisions d ON d.divisionID = c.divisionID Inner Join groupsOfEight e ON e.groupLeaderID = b.groupLeaderID Inner Join groupOfEightMembers f ON f.groupOfEightID = e.groupOfEightID Group BY a.memberFirstName, a.memberLastName, c.chapterName, d.divisionName Order By divisionName, numMembers This query returns me the names of all of my Group Leaders, their Chapter, Division, and the number of members they have selected to be in their group. Now, instead of the number of members in each Group I would like to know the total number of Members in each division to appear in the count. [NOTE: All chapters have a division, linked by a divisionID in the "Chapters" table -- I need to get a count of all the "ChapterMembers" [chaptermembers is a table also] that are in the Division. Here is the query I started to build before I ran into serious trouble: Quote: select a.divisionName, count('c.memberID') as numMembers From Divisions a Inner Join Chapters b On b.divisionID = a.divisionID Inner Join chapterMembers c ON c.chapterID = b.chapterID Left Join Members d ON d.memberID = c.memberID LEFT Join groupLeaders e On e.memberID = d.memberID Group By a.divisionName This particular query returns only the DivisonName and the number of Members in the division as expected. However, when I try to select the information for the GroupLeader (first & last name) I am forced to add memberFirstName to the Group By statement which changes my Count... Have I done an okay job of explaining the problem? The goal here is to select all of the GroupLeaders first & last name, their chapterName, divisionName, and the total number of members in the division. Thanks for any advice! Zoop
View Replies !
SQL Select To Avoid Use Of Aggregate Function MAX
Can you provide me a way to avoid using the aggregate function Max in the following select statement? SELECT a.clmssn, a.cossn, Max(b.clm_seq_num) as clm_seq_num, Max(c.chrs_seq_num) as chrs_seq_num, Max(b.clm_dcn_dt) as clm_dcn_dt FROM Claim a LEFT OUTER JOIN clmdcn b ON a.clmssn = b.clmssn AND a.cossn = b.cossn AND a.clm_seq_num = b.clm_seq_num INNER JOIN clmchrs c ON a.clmssn = c.clmssn AND a.cossn = c.cossn AND a.clm_seq_num = c.clm_seq_num Group By a.clmssn, a.cossn Order By a.clmssn, a.cossn I want to join another table, Juris, to get the Crnt_Office column but the aggregate function Max forces me to include Crnt_Office it in the group by clause. This results in returning all rows containing unique office codes. I only want the office code from the row with the highest Clm_Seq_Num and Juris_Seq_Num. Juris Clmssn = key Cossn = key Clm_seq_num = key Juris_seq_num = key Crnt_Office The following returns incorrect results: SELECT a.clmssn, a.cossn, Max(a.clm_seq_num) as clm_seq_num, Max(c.chrs_seq_num) as chrs_seq_num, Max(d.juris_seq_num) as juris_seq_num, Max(b.clm_dcn_dt) as clm_dcn_dt, d.crnt_office FROM mt16ic_Claim a LEFT OUTER JOIN mt16ic_clmdcn b ON a.clmssn = b.clmssn AND a.cossn = b.cossn AND a.clm_seq_num = b.clm_seq_num LEFT OUTER JOIN mt16ic_juris d ON a.clmssn = d.clmssn AND a.cossn = d.cossn AND a.clm_seq_num = d.clm_seq_num INNER JOIN mt16ic_clmchrs c ON a.clmssn = c.clmssn AND a.cossn = c.cossn AND a.clm_seq_num = c.clm_seq_num Group By a.clmssn, a.cossn, d.crnt_office Order By a.clmssn, a.cossn If more information is needed, I have data examples but could not include them due to character size limit. Thank you for your help.
View Replies !
Multi Parameter Aggregate CLR-function?
In the provided CLR-function sample there is only one parameter from the SQL statement to the function. I can't find any info about how to pass more then one parameter. Is this possible somehow? like: Code Snippet Select ShopCode,PickAValue(OrderDate,Article) as BestArticle from T_Sales Group by ShopCode hans
View Replies !
CLR User Defined Aggregate Function
I am trying to modify the C# example given in the "Invoking CLR User-Defined Aggregate Functions" in SQL Server 2005 Books Online. I want to add a parameter that is used as the list delimeter, instead of the hard coded comma that is used. I am not a C# programmer and don't know where to begin. Please help! using System; using System.Data; using Microsoft.SqlServer.Server; using System.Data.SqlTypes; using System.IO; using System.Text; [Serializable] [SqlUserDefinedAggregate( Format.UserDefined, //use clr serialization to serialize the intermediate result IsInvariantToNulls = true, //optimizer property IsInvariantToDuplicates = false, //optimizer property IsInvariantToOrder = false, //optimizer property MaxByteSize = 8000) //maximum size in bytes of persisted value ] public class Concatenate : IBinarySerialize { /// <summary> /// The variable that holds the intermediate result of the concatenation /// </summary> private StringBuilder intermediateResult; /// <summary> /// Initialize the internal data structures /// </summary> public void Init() { this.intermediateResult = new StringBuilder(); } /// <summary> /// Accumulate the next value, not if the value is null /// </summary> /// <param name="value"></param> public void Accumulate(SqlString value) { if (value.IsNull) { return; } this.intermediateResult.Append(value.Value).Append(','); /// I want to change to comma to a variable } /// <summary> /// Merge the partially computed aggregate with this aggregate. /// </summary> /// <param name="other"></param> public void Merge(Concatenate other) { this.intermediateResult.Append(other.intermediateResult); } /// <summary> /// Called at the end of aggregation, to return the results of the aggregation. /// </summary> /// <returns></returns> public SqlString Terminate() { string output = string.Empty; //delete the trailing comma, if any if (this.intermediateResult != null && this.intermediateResult.Length > 0) { output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1); } return new SqlString(output); } public void Read(BinaryReader r) { intermediateResult = new StringBuilder(r.ReadString()); } public void Write(BinaryWriter w) { w.Write(this.intermediateResult.ToString()); } }
View Replies !
Aggregate Function Task Issue
We have a Data Flow Task. Inside this, we have a OLE DB Data Source which calls and executes a stored procedure. We use a Row Count Task to count the number of rows returned by the OLE DB Data Dource Task. Then, we use an aggregate function task to get the sum of all the rows of one particular column that is returned from the OLE DB Data Source. The issue here is that we want to assign the sum value returned by the Aggregate Function Task to a User Variable named User::Variable. We have tried to assign this by using @User::Variable and User::Variable, but neither of those return the expected value. If there is any other method to do the same then let us know. We have checked the that the row count is greater than zero. Any help would be very much appreciated. Thanks in advance.
View Replies !
Aggregate Function - Group Scope
Hi There, I need to sum some group totals in higher groups. A regular sum doesn't work for this case, so I am trying to add a group scope to the sum function. =sum(Fields!GrossHours.Value,"TableRow6") =sum(Fields!GrossHours.Value,"GroupName") =sum(Fields!GrossHours.Value,"EmployeeNameGroup") I am trying to sum the value in TableRow6 (which is the group 4 header) in group 3. The name of the group in the group sorting properties dialog box is: EmployeeNameGroup. All my attempts get the same error: The value expression for the textbox has a value that is not valid for an aggregate function. The scope parameter must be set to a string constant equal to the name of a containing group, containing data region or the name of a dataset. Thanks for reading. Paul O
View Replies !
Aggregate Function/Ambiguous Trouble
I'm a newb and this has been giving me a trouble for a while. Code Block SELECT class.classcode, classname, instrFirst, instrLast, csDay, CONVERT(nvarchar(30), csStart, 8), CONVERT(nvarchar(30), csEnd, 8) FROM class, class_section, instructor WHERE class.classcode = class_section.classcode Group By className Order By classname, csNum; Msg 8120, Level 16, State 1, Line 1 Column 'class.classCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. class and instructor and class_section are their own tables but classcode and instrID are foreign keys on the class_section table. If anyone can explain this as clearly as possible that would be great (the help examples are usually too complicated)
View Replies !
UPDATE RECORDS WITH AGGREGATE FUNCTION
Problem is very simple I want to update sum of a field from another table to first table TABLE ONE: ========== ItemID QtyInStock Table TWO: ========== BatchID ItemID Qty I want to Update the QtyInStock of First Table with Sum(Batch.Qty) here is the query i am writing but giving error UPDATE ITEMS SET INSTOCKQTY=CASE WHEN QtyInBatch>1 THEN QTYINBATCH ELSE 0 END FROM ITEMS, ( SELECT ITEMS.ITEMID, SUM(Batch.Qty) AS QtyInBatch FROM Batch INNER JOIN Items ON Batch.ItemID = Items.ItemID GROUP BY ITEMS.ITEMID ) appericiating anyones help in advance FAZEEL AMJAD Systems Engineer Crystal Technologies
View Replies !
Update Table With Joins And Aggregate Function
Is this possible? What I am looking for is something like:UPDATE T_SitesSET T_Sites.LastDate = T_Inspections.DateFROM T_SitesINNER JOIN T_Assets ON T_Sites.SiteID = T_Assets.AssetIDLEFT OUTER JOIN T_Insecptions ON T_Assets.AssetID = T_Inspections.AssetID-- But I need only the last inspection done on the site (including if it is null)
View Replies !
View With Aggregate Function And Joined Tables
I have created the following view: Create view vwOrderItemTotal2 AS SELECT ItemName, fkMenuItemID, Sum(Quantity) as [SumOfMenuITems] FROM OrderItems GROUP BY fkMenuItemId, ItemName When I present my data in a GridView, it works fine. For example, several orders for milk are returned as a summary quantity of 26 gallons in a single row of the GridView like this: 26 Milk Now I need to filter my data by OrderDate and Zipcode. I created this new view: Create view vwOrderItemTotal5 AS SELECT Orders.Zipcode, Orders.OrderDate, OrderItems.ItemName, OrderItems.fkMenuItemID, Sum(Quantity) as [SumOfMenuITems] FROM Orders INNER JOIN OrderItems ON Orders.OrderID = OrderItems.fkOrderID GROUP BY fkMenuItemId, ItemName, Zipcode, OrderDate When I present my data in a Gridview using the new view I get a GridView with multiple rows for milk where each order has its own row like this: 1 Milk 5 Milk 6 Milk 6 Milk 3 Milk 1 Milk 4 Milk But I want the data presentation in one row for each ItemName (e.g. Milk) as with my first view. Can I adjust my new view to achieve this, or should I stick with my first view (vwOrderItemTotal2) and adjust the Select Command in my SqlDataSource (hasn’t worked yet). I think that what I want is for the returned data to be grouped by fkMenuItemId only, but the sql server admin won’t let me create a view without including the other fields in the Group By clause. Thanks for any help provided in solving this.
View Replies !
Aggregate Function For Select Statement Result?
Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls. currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do. Problem: i need to call an aggregate count on the results of another aggregate function (sum) with a group by. *ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid. thanks, Tom Anderson Software Engineer Custom Business Solutions
View Replies !
2005: Creating Aggregate Function From .NET Assembly
Hello,I am learning SQL Server 2005. I have (correctly) written in .NETassembly DemoSQLServer with aggregate function AvgNoMinMax in classDemo and I have added assembly to database DemoSQLServer. Now I needto create aggregate in SQL Server. I tried this way:CREATE AGGREGATE AvgNoMinMax(@v float) RETURNS float EXTERNAL NAME[DemoSQLServer].[DemoSQLServer.Demo].[AvgNoMinMax]Unfortunately I have error:Incorrect syntax near '.'.I don't know what's wrong. Please help.Thank you very much!/RAM/
View Replies !
|
TRACKER
