Transact SQL :: Invalid Due To Not Being In Group By Or Aggregate Function
Aug 7, 2015
Well adding it to a group by or function skews the result set. How to write this query so it displays as I need it to? This is what I have thus far, and it works as it should UNTIL I add in the line of
cast(cte.[C] As float)/cast(sum(cte.[C]) over() as float)*100 As [Rate1],
Presents the error of:
Msg 8120, Level 16, State 1, Line 35
Column 'cte.[C]' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
This is my full on query -- with 3 CTE's involved to get me the actual result set I am after.
;with cte as
(
select
[state],
case when exists (select 1 from table2 R where R.centername = d.centername) then 1 else 0 end as [L],
case when exists (select 1 from table3 C where C.centername = d.centername) then 1 else 0 end as [C]
FROM maintable d
),
I have a specific variation on the standard 'Column Invalid' question: I have this query that works fine:
SELECT vd.Question , csq.Q# , csq.Q_Sort , csq.Q_SubSort , AVG(CAST(vd.Response AS FLOAT)) AS AvgC , vd.RType
[Code] ....
When I add this second average column like this:
SELECT vd.Question , csq.Q# , csq.Q_Sort , csq.Q_SubSort , AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
[Code] ....
I get the error: Column 'dbo.vwData.Response' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Clearly things are in the right place before the change, so I can only assume that the OVER clause is my problem. Is this just not possible?
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
I have a problem with an SQL-query and I don't know what the best solution would be to solve the problem.
/*INSERT INTO WERKS ( WERKS.Z8601, WERKS.Z8602, WERKS.Z8603, WERKS.Z8604, WERKS.Z8605, WERKS.Z8606, WERKS.Z8607, WERKS.Z8608, WERKS.Z8609, WERKS.Z8610, WERKS.Z8611, WERKS.Z8621, WERKS.Z8622, WERKS.Z8623, WERKS.Z8624, WERKS.Z8625, WERKS.Z8626, WERKS.Z8627, WERKS.Z8628, WERKS.Z8629, WERKS.Z8630, WERKS.Z8631, WERKS.Z8632) */ SELECT 0, Stati.z4414, Stati.z4402, '', '', '', Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), ''), Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), ''), '', '', '', sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End), sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End), sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End), sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End), sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End), sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End), sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End), sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End), sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End), sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End), sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End), sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End) FROM STATI WHERE (Stati.z4402 Between '40000' AND 'ZONE6') AND (Stati.z4414 Between '2005028' AND '2005028') AND (Stati.z4417 = 'A') GROUP BY Stati.z4414, Stati.z4402
I get the following error:
Msg 8120, Level 16, State 1, Line 25 Column 'STATI.Z4400' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I know it has something todo with the select on the table SRTAS, but what's the best way to solve this problem without the chance of getting a wrong result.
The SELECT on SRTAS is to get the "description" of STATI.Z4414 who's stored in the table SRTAS. I only want to group on the fields that will be inserted in WERKS.Z8602, WERKS.Z8603, WERKS.Z8604, WERKS.Z8605, WERKS.Z8606. So adding STATI.Z4400 to the GROUP BY would give me wrong results?
This query is dynamicly generated from within my program from what the user selected.
Also, if there are better ways to write the query, I would be happy to get some hints and tips, but if possible without stored procedures.
I am using SQL 2005. I have some data from an old application that did not follow the rules for normalization. The table is for Invoices, and the table allows for 13 purchase items per record. So in each row of my table I have a non-unique integer field itemID, itemID1, itemID2 ... itemID12. For each itemID I also have "lbs_total" and "line_total" (which is price * lbs_total) - so itemID, lbs_total, line_total ... itemID1, lbs_total1, line_total1 ... etc. It's a mess, I know.Each row has a unique Customer Number ("cno") and an Invoice Date ("inv_date"). My proc needs to allow for params for the item number, and a start date and end date for BETWEEN on the inv_date.I also need to get the aggregate for the lbs_total and the line_total.
best solution for this stored procedure query.I'm getting the following error:
Column 'dbo.Applicants.submitted' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Here is my select query:
Code: SELECT DISTINCT DATENAME(MONTH, submitted) AS mon, MAX(submitted) AS SubmitDate FROM dbo.Applicants WHERE ((CASE WHEN MONTH(submitted) > 8 THEN YEAR(submitted) + 1 ELSE YEAR(submitted) END) = @AcYr) ORDER BY SubmitDate
The submitted field is a date field.I don't want to add the submitted field to Group By as I want to group by month not date.Is there any solution to avoid grouping by date?
SSMS does not like mine! THis is the error that I receive:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
And this is my syntax:
Select employeeID ,COUNT(case when rehirestatus IN (select rehirestatus from regionalemptable where rtrim(storename) = 'Location1') THEN userID ELSE 0 END) + COUNT(case when rehirestatus IN (select rehirestatus from globalemptable where rtrim(storename) = 'Location1') Then userID ELSE 0 End) FROM production GROUP BY employeeID ORDER BY employeeID
While running the below query, getting the error: Am I missing any of the columns to include in the SELECT column_list?
Msg 8120, Level 16, State 1, Line 1
Column 'sys.master_files.database_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
<code> select a.[Database Name],a.[Type],a.[Size in MB],b.LastUserUpdate from ( SELECT database_id,[Database Name]= DB_NAME(database_id), [Type]= CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)' WHEN Type_Desc = 'LOG' THEN 'Log File(s)' ELSE Type_Desc END ,
I'm trying to write a query to select various columns from 3 tables. In the where clause I use a set of conditions, but most important condition is that I only want to see all results from the different columns where the ph.ProdHeaderDossierCode contains at least 25 lines of processed hours. I tried this with group by and having, but I constant get error messages on all other columns that I want to see: "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". How can I make this so I can see all information I need?
I have a a Group By query which is working fine aggregating records by city. Now I have a requirement to focus on one city and then group the other cities to 'Other'. Here is the query which works:
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' From [Output-MarketAnalysis] Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active' Group by [City]
Here is the result:
St. Louis 1000 Kansas City 800 Columbia 700 Jefferson City 650 Joplin 300
When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' From [Output-MarketAnalysis] Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active' Group by [City]
Here is the result:
St. Louis 1000 Other Missouri City 800 Other Missouri City 700 Other Missouri City 650 Other Missouri City 300
I have a few tables I am trying to join to create a report. Everything was working fine until I tried to add an aggregate Sum function to a column (MaxCap) in table ctfBarn.
I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?
Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.
select order_num, sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >=50 order by ordertotal;
I'm trying to use the aggregate transformation to sum my orders table unit price and quantity with a grouping of state but i can't see how to add the sub grouping. My order table has the following fields of interest Unit Price (Money), Quantity (Integer) and State (Varchar)
ID Unit Price Quantity State
1 $2.19 500 AZ
2 $29.99 33 WA
3 $1000.00 1 WA
4 $1.20 7 WA
When i run the aggregate i want the output to be grouped by state
Hello, everyone!Does anyone know how I can pull additional field in a database whenthe max() of one field is pulled. For example:================================================== ===SELECT TOP 200 foreign_id, MAX(recordcreateddatetime) ASmax_recordcreateddatetimeFROM table1GROUP BY foreign_id================================================== ===Here I am trying to pull the records that have the latest date foreach foreign_id. The result set above will pull foreign_id andmax_recordcreateddatetime but I needed to also have it display onemore field, current_status like this:================================================== ===SELECT TOP 200 foreign_id, MAX(recordcreateddatetime) ASmax_recordcreateddatetime, current_statusFROM table1GROUP BY foreign_id================================================== ===The problem however is that SQL wants me to add this field to GROUP BYor use an aggregate function with it and I don't want any aggregatefunction processing - I just want current_status to show up there forme to see. How do I do this?Thank you for any input in advance!Roumen.
Installed the Northwind database for data to practice with. I'm trying to combine data from several tables to make the orders table more readable. Meaning, I'm trying to replace the EmployeeID field with the combination of the firstname and lastname fields from the Employees table. Everything works fine until I try to sum the Unit price field from the [Order Details] table. Using just a SUM() function or the Select statement below causes the error and any combination of fields in the Group By command don't correct it. It's clear that I'm doing something wrong, I'm just not sure how to get the data I want or use the group by command properly. Query below:
Select o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName, o.Freight, o.shipName, o.ShipAddress, (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount from orders o, customers c, Employees e, Shippers s, [Order Details] od where o.CustomerID = c.CustomerID
[Code] ....
Running the first query (with the select statement) works, but returns a row for each of the the items that was ordered for that OrderID and NOT using the Group By. I would like to have the SUM() of the items ordered in one row. Is this possible?
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)
I am having trouble with a particular query that is beyond my scope of understanding.
Basically I need to pull sales records based on the following criteria:
I have CustomerID, InvoiceNumber, ContractEndDate, MobileNumber, etc..
Customers recontract their mobile phone plans through us, and we have a new sales record for each time they recontract.
For example, CustomerNumber 123 has recontracted 3 times..
once on 2006-01-01, then on 2007-02-12, and finally on 2008-02-15..
So they have a 12 month contract each time.. then come in to recontract it.
So.. a customer has a single Customer Detail record, but may have many sales records attached. And a customer may have several sales for the SAME mobile phone number.
Currently to pull ALL sales records for all customers, my query is this:
Code:
SELECT xxx.CustomerID AS xxx_CustomerID, xxx.Invoice AS xxx_Invoice, yyy.PhoneType AS yyy_PhoneType, yyy.PlanType AS yyy_PlanType, yyy.ContractEnds AS yyy_ContractEnds, yyy.MOB AS yyy_MobileNumber
FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice
WHERE yyy.ContractEnds IS NOT NULL AND xxx.CustomerID IS NOT NULL
We want to get a list of customers that we can call to recontract, based on the ContractEnd field.
However, we want UNIQUE mobile phone numbers, with the LATEST ContrtactEnd date.
So, Customer 123 has 6 sales, for 2 unique Mobile numbers, the sql may be like:
Code:
SELECT MAX(yyy.ContractEnds) AS LatestCED, yyy.MOB FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice WHERE xxx.CustomerID='123' GROUP BY yyy.MOB
Now, this works fine, and of course if i remove the WHERE clause, it collects all unique mobiles, with latest ContractEnd date for each, for all customers. (Customer 123 displays 2 mobile numbers, each with the LATEST ContractEnd date)
BUT i need this information ALONG WITH the other fields (xxx.CustomerID, xxx.Invoice, yyy.PhoneType, yyy.PlanType) and i have tried a few ways of doing it, but can't get my head around it..
Keep getting errors about Aggregate functions and Group By clause, and i understand why i am getting them, just cant think of any alternative query.
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;
