Stored Procedure - Field Not In Aggregate Function Or Group By
Nov 29, 2011
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?
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.
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 ),
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 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
SSRS/SSAS. I'm using aggregate function no problem in a Table, but when I try to use same function in data field of a chart I get no results. I know that "aggregate" has only been supported since SP2, is there a known problem with using it in charts?
Hi everybody, I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005 Stored procedure:
Alter PROCEDURE [dbo].[sp_GetIdeaByCategory] @CatId <span class="kwd">int</span> = 0 AS BEGIN SET NOCOUNT ON;
Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc End
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
Has anyone encountered this before? Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied. It seems that my code is not passing the parameter to the stored procedure. When I click this hyperlink: <asp:HyperLink ID="HyperLink1" Runat="server" NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>' Text='<%# Eval("Name") %>' ToolTip='<%# Eval("Description") %>' CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'> </asp:HyperLink> it is suppose to get the country name and description, based on the country id. I am passing the country id like this. protected void Page_Load(object sender, EventArgs e) { PopulateControls(); } private void PopulateControls() { string countryId = Request.QueryString["CountryID"]; if (countryId != null) { CountryDetails cd = DivisionAccess.GetCountryDetails(countryId); divisionNameLabel.Text = cd.Name; divisionDescriptionLabel.Text = cd.Description; } } To my app code like this: public struct CountryDetails { public string Name; public string Description; } public static class DivisionAccess { static DivisionAccess() public static DataTable GetCountry() { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetCountry"; return GenericDataAccess.ExecuteSelectCommand(comm); } public static CountryDetails GetCountryDetails(string cId) { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetCountryDetails"; DbParameter param = comm.CreateParameter(); param.ParameterName = "@CountryID"; param.Value = 2; param.DbType = DbType.Int32; comm.Parameters.Add(param); DataTable table = GenericDataAccess.ExecuteSelectCommand(comm); CountryDetails details = new CountryDetails(); if (table.Rows.Count > 0) { details.Name = table.Rows[0]["Name"].ToString(); details.Description = table.Rows[0]["Description"].ToString(); } return details; }
As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied Someone please help!
When I am trying to call a function I made from a stored procedure of my creation as well I am getting:
Running [dbo].[DeleteSetByTime].
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[DeleteSetByTime].
This is my function:
ALTER FUNCTION dbo.TTLValue
(
)
RETURNS TABLE
AS
RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'
This is my stored procedure:
ALTER PROCEDURE dbo.DeleteSetByTime
AS
BEGIN
SET NOCOUNT ON
DECLARE @TTL int
SET @TTL = dbo.TTLValue()
DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)
END
CreatedTime is a datetime column and TTL is an integer column.
I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.
I am trying to to a transaction count (per day) running a stored procedure that does a group by date. The problem is that since the date has a time stamp (I assume) it sees each date as a different group even if it's the same day. Is there a way to format the date in the stored procedure so that it sees all of the transactions on the same day as one or is there another way to do this. Select count(recordid),transactiondatefrom sometable group by transactiondate Thanks in advance
What is the easiest way to accomplish this task with SSIS?
Basically I have a stored procedure that unions multiple queries between databases. I need to be able to export this to a text file on a daily basis and add a total records: row to the end of the text file.
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.
I thought that procedures do not return a value: well they only return a 1 or 0 depending on whether the procedure executed correctly. Functions are what you're supposed to use when you want to get a result set of some sort: ie a table or a scalar value.
Apparently that is not the case becuase you can return values from procedures. I've attempted to find out what the differences are between the two and when it is appropriate to use a procedure and when it is appropriate to use a function but I'm still unsure. Can someone please tell me the difference.
I have a query that brings back the data below. I need to divide the BudgetTotal by the Count. Then I need to go to the records that make up those €œgroups€? and enter a Budget value = BudgetTotal/Count.
How could I write this in a stored procedure or a SQL statement if possible?
Thanks.
Kevin
SELECT TOP 100 PERCENT dbo.ReportTable.ProjectNo, dbo.ReportTable.Category, dbo.ReportTable.Type, COUNT(dbo.ReportTable.ProjectNo) AS count, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget AS Expr1 FROM dbo.ReportTable INNER JOIN dbo.OracleDownloadBudget ON dbo.ReportTable.Category = dbo.OracleDownloadBudget.Category AND dbo.ReportTable.ProjectNo = dbo.OracleDownloadBudget.Project AND dbo.ReportTable.Type = dbo.OracleDownloadBudget.Type GROUP BY dbo.ReportTable.ProjectNo, dbo.ReportTable.ProjectName, dbo.ReportTable.Category, dbo.ReportTable.Type, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget HAVING (dbo.ReportTable.Budget < 1) ORDER BY dbo.ReportTable.ProjectNo
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?
Hi All, I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions: 1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject. 2) Can someone provide any links to online tutorials on t-sql? Thanks in advance. Curtis
Hello all, I'm trying to construct a select statement in a stored procedure that filters based on the returned values of a number of functions. My function works fine, but when I try to call the function from the stored procedure I get an error. I'm going to try explain the thought process behind what I'm doing. Hope I make enough sense.The purpose of the stored procedure is to perform a wildcard search on a tool. The tool contains a number of FK that link to different tables (e.g., manufacturer, vendor). So I'm creating functions that also search the manufacturer and vendor and return the matching IDs. Example of tool SELECT statement:SELECT tool_number, tool_description FROM tool WHERE tool_manufacturer IN (UDFmanufacturer_SearchName(@search_string) This gives me an error:'UDFmanufacturer_SearchName' is not a recognized built-in function name. Function code (removed some wrapping code for simplicity):SELECT manufacturer_id FROM manufacturer WHERE manufacturer_name LIKE '%' + @search_string + '%'These statements both work if I run a independent query: SELECT * FROM UDFmanufacturer_SearchName('mol') SELECT * FROM tool WHERE tool_manufacturer IN (SELECT *FROM UDFmanufacturer_SearchName('mol')) This code fails:SELECT * FROM ato_tool WHERE ato_tool_manufacturer IN (UDFmanufacturer_SearchName('mol')) I'm stuck. I haven't been able to find anything that shows me where I'm going wrong. Any thoughts or suggestions are appreciated. Thanks,Jay
Hi, I ran into some problem here. The case scenerio is supposed to be like this:
- Each member can only make one appointment at any one time and only make another appointment after the existing appointment expired. - Each member is allowed to make an appointment at any time and must be at least 5 days in advance.
I managed to do the reservation for at least 5 days in advance but I can't allow the member to make only one appointment. The member can keep making appointments even though the existing appointment has not expired. Can someone pls help? Thanks!
ALTER PROCEDURE spReserveAppt(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9)) AS
BEGIN IF NOT EXISTS(SELECT MemNRIC FROM DasMember WHERE MemNRIC = @MemNRIC) RETURN -300
BEGIN IF EXISTS (SELECT COUNT(@MemNRIC) FROM DasAppointment WHERE (DATEDIFF(DAY, GETDATE(), @AppDate) < 5) GROUP BY MemNRIC HAVING COUNT(@MemNRIC) <> 0) RETURN -301
ELSE IF EXISTS (SELECT MemNRIC FROM DasAppointment WHERE (DATEDIFF(DAY, @AppDate ,GETDATE()) > GETDATE())) RETURN -302
END END
INSERT INTO DasAppointment(AppDate, AppTime, MemNRIC) VALUES (@AppDate, @AppTime, @MemNRIC)
I have a select statement like this. Notice that I am doing the same calculation on different fields. Is it possible I can make this, in coding terms, a fucntion; so I can call it when I like (this query) and just provide the field.
Example: select field1, test_field = secs_to_hhmmss(TALK_TIME) from ...
SELECT dbo.date_table.real_date, dbo.time_table.hh, COUNT(dbo.CALLDETAIL.id) as NumOfCalls, TalkTime = CASE WHEN CAST(SUM(TALK_TIME) AS INTEGER)/3600<10 THEN '0' ELSE '' END + RTRIM(CAST(SUM(TALK_TIME) AS INTEGER)/3600) + ':' + RIGHT('0'+RTRIM((CAST(SUM(TALK_TIME) AS INTEGER) % 3600) / 60),2) + ':' + RIGHT('0'+RTRIM((CAST(SUM(TALK_TIME) AS INTEGER) % 3600) % 60),2), HoldTime = CASE WHEN CAST(SUM(HOLD_TIME) AS INTEGER)/3600<10 THEN '0' ELSE '' END + RTRIM(CAST(SUM(HOLD_TIME) AS INTEGER)/3600) + ':' + RIGHT('0'+RTRIM((CAST(SUM(HOLD_TIME) AS INTEGER) % 3600) / 60),2) + ':' + RIGHT('0'+RTRIM((CAST(SUM(HOLD_TIME) AS INTEGER) % 3600) % 60),2),