Cannot Perform An Aggregate Function On An Express
Dec 27, 2007
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.
I am getting error by executing below query, I need to calculate the percentage.
select oe2.OrdSourceID,Count(oe2.OrdSourceID) as TotalOrders, Percentage = Count(oe2.OrdSourceID) * 100.0 / SUM(Count(oe2.OrdSourceID)) from OeOrders Oe iNNER JOIN OeOrders2 AS oe2 ON Oe.OrderID = oe2.OrderID where Oe.ProviderID = 'JOHN' and Oe.OrderDateTime between '10/07/2014' and '10/15/2014' and oe2.OrdSourceID is not null AND Oe.[Status] NOT IN ( 'CANCEL', 'CANC', 'CNC', 'UNVER', 'UNV' ) Group by oe2.OrdSourceID
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"
I get an error dialog when I try to create a new SQL database, both via the Add New Item dialog and the property wizard of a new SqlDataSource control. The error is:
Local Database File:
User does not have permission to perform this action.
I've searched for help with this.
I ensured the App_Data folder exists and I added the local ASP.NET account to the group that have R/W access to it (although the RO flag is in an unchangeable tri-state on the folder). The SQL Server Express error log is clean and indicates full functionality. Everything is running locally. No VWD installation errors.
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.
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.
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)
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.
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.
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.
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.'
If you use Coalesce with Count will it return all values even when null? Reason I pose this question to you is that I am running the below query and am getting escalated results than what should be returned. 1st stop on the debug train is how does Coalesce handle it....for example Boston should return only 143 but the query retunrs 194 for Boston?
Code: Select Count(NumOnsite), originatingCity, Coalesce(Convert(varchar(4000),NewspaperNames), Convert(varchar(4000),MagazineNames)) As PaperNames From readytoshipOffsite Group By originatingCity, Coalesce(Convert(varchar(4000),NewspaperNames), Convert(varchar(4000),MagazineNames))
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;
I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.So in this case I am using this syntax to show the most recent quote within the project.
SELECT PROJECT.*, QUOTE.QuoteDate, QUOTE.QuoteCode FROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectID WHERE QUOTE.QuoteDate=(SELECT Max(Q.QuoteDate) FROM QUOTE Q WHERE Q.ProjectID = PROJECT.ProjectID);
My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.
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.
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.
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'
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.
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.
I am using Visual Basic 2008 Express Edition and Microsoft SQL Server Database (or CE 3.5), and I need to be able to:
Create an open connection
Insert rows of data
Read rows of data
Delete rows of data
Close and dispose of the connection I found some code examples out there, but nothing current and specific to VB2008 and MS SQL SD (or CE). If you could point me to sample code that covers this for the 2008 versions of VB and SQL that would be great! Thanks.Techno
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
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.
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.
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??
