Search the Community

Hi guys, i need to finish an assignment but keep getting the error: ERROR 1111 (HY000): Invalid use of group function The assignment goes as follows: Give a list of cruises (c_code, description) for all cruises and their number of passengers that have more passengers then average. There are 3 tables in the database: Booking, cruises and passengers. My code is :SELECT C_CODE, C_DESCRIPT, COUNT(DISTINCT(B_PASS)) FROM CRUISE, BOOKING WHERE (CRUISE.C_CODE =BOOKING.B_CRUISE) GROUP BY C_CODE HAVING COUNT(DISTINCT(B_PASS)) > AVG(COUNT(DISTINCT(B_PASS))); Obviously I'm doing something wrong.. Does anyone know what I'm doing wrong ? thanks in advance, Omoruyi

I have a database of 5 tables. 1st table is my customer table. 2nd is my crust table. 3rd is my toppings table. 4th is my sides table. 5th is my order table. My crust, toppings and sides table all have a price column and I am trying to get them to add together on my order table total cost column. What function would be the best to use to accomplish getting the crust price toppings price and sides price to add up on the order table Total cost column? Here is my table schema: Crust TableCrustID Primary KeyCrustnameCrustsizeCrustprice moneyToppings TableToppingID Primary KeyToppingNameToppingPrice moneySides TableSideID Primary KeySideNameSidePortionSidePrice moneyCustomer TableCustomerID Primary KeyFnameLnameStreetCityStateZipPhonenumberemailaddress Order TableOrderId Primary Key INTCustomer Foreign Key INTCrust Foreign Key INTToppings Foreign Key INT Sides foreign key int TotalCost money

My goal is to have users fill out three fields and click on a button that will check an sql table to see if there is a record with those three fields already in the database. I'm not 100% sure, but I think my issue is in my C# code calling the procedure. I have tested my stored procedure from sql and it works. I've tried a few things, but nothing is working. The code below is one of my attempts that isn't working. I'm not sure, but when I step through the program it doesn't appear to be calling the procedure (though I could be wrong and the problem is elsewhere). SqlConnection conn = new SqlConnection(GetConnectionString()); try { conn.Open(); SqlCommand cmd = new SqlCommand("SubmitCheck", conn); SqlParameter sqlCheck = new SqlParameter("@result", DbType.Int16); cmd.Parameters.AddWithValue("@pfName", fName.Text); cmd.Parameters.AddWithValue("@plName", lName.Text); cmd.Parameters.AddWithValue("@pssn", ssn.Text); cmd.CommandType = CommandType.StoredProcedure; bool check = Convert.ToBoolean(cmd.ExecuteScalar()); if (check) { checkEntrylbl.Text = "You have not yet completed this form. Please complete the form."; checkEntrylbl.Visible = true; lName.Enabled = false; fName.Enabled = false; ssn.Enabled = false; submitButton.Enabled = true; } else { checkEntrylbl.Text = "You have completed this form. You do not have to fill it out again."; checkEntrylbl.Visible = true; } } catch (System.Data.SqlClient.SqlException ex) { string msg = "Insert Error:"; msg += ex.Message; throw new Exception(msg); } finally { conn.Close(); } Here is the stored procedure code. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE SubmitCheck -- Add the parameters for the stored procedure here @pfName varchar(50), @plName varchar(50), @pssn int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Checks if there is an entry with the same first name, last name, and last four of ssn. IF (EXISTS(SELECT fName, lName, ssn FROM dbo.compassSurvey WHERE @pfName = fName AND @plName = lName AND @pssn = ssn)) BEGIN RETURN (0); END BEGIN RETURN (1); END END Thanks for any suggestions.

Anyone have any suggestions.?? I have Table <customers> I need to query the data points in Column ID ( data type_ Int ) and Column Lot (data type_Int) FROM customers but what my query is doing is adding this two data points up. and I don't want a SUM result. ex. if ID = 100 and Lot = 543 my query result = 643 what I am looking for is IF ID=100 and Lot = 543 my query result should be= 100543 SELECT customers.ID + ' ' +customesr.Lot AS combined FROM customers ANY SUGGESTIONS PLEASE!!!

Anyone have any suggestions.?? I have Table <customers> I need to query the data points in Column ID ( data type_ Int ) and Column Lot (data type_Int) FROM customers but what my query is doing is adding this two data points up. and I don't want a SUM result. ex. if ID = 100 and Lot = 543 my query result = 643 what I am looking for is IF ID=100 and Lot = 543 my query result should be= 100543 SELECT customers.ID + ' ' +customesr.Lot AS combined FROM customers ANY SUGGESTIONS PLEASE!!!

Soooo I am taking a class called Electronic Commerce. My professor is is requiring us to make a website "selling" a product (not for real) and the website must use html 5, php and sql. He is not going to waste class time teaching us these because this is a "capstone" class and we should already be familiar. Well Html 5 I am ok with, but SQL and PHP, I am so lost. As far as I can figure out, the SQL is for my database. I have already gathered the jpgs, made a database of the spices with prices I will sell. I am not really sure how to attack this project and any help will by greatly appreciated. There is no mention of this project in his syllabus and folks that have taken his class before said that he doesn't even grade this project, I really just want to have something to turn in just in case. Thanks

Hello.I have this code $get_cart_info = mysql_query("select c.*,g.*,m.* from cart as c left join games as g on c.tid = g.id left join movies as m on c.tid=m.id where c.user = {$CURUSER['id']} GROUP BY c.tid") or die(mysql_error()); But what I need is when c.type is movie its only takes info from movies tabel, but still have to take everything out, because I both have movies and games in cart..Can it be done, or do I have to trying something else?

Sir; I have developed a website for my Department of Science, which recently is hosted in USA. The website is programmed not to accept any form submissions after 8 pm. Also it gets redirected to "Site is down" page on Saturdays and Sundays. It was OK while the site was over in-house server. Now that its in USA, because the time zone now is different, it does not accept the forms after 8 pm in USA, which is 8 am here in India. Site does not accept forms from 8 am to 12 noon here in India. Also weekend redirect to "Site is down" is of no use due to date being different in USA and in India. Is there any code in ASP or procedure in SQL 2008 R2, whereby I can convert the USA time on the server in USA to Indian Standard Time ( IST ) so that the required functioning of the site happens as per the Indian Time and not as per the time in USA? We are on shared server so the server time can not be changed. Thanks DAMODAR G AGNI

Hi,I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required. Really appreciate your expertise. Sorry if my explaination below is quite lengthy. SCENARIO : I am required to query from 2 tables for those unique record that meets both conditions below:-1. Status is 1 @ max (trans_id), paychnl = CC2. Status is 2 @ max (trans_id), paychnl = A or B FYR, 2 tables and respective columns to query are as below:-table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODEtable CHFF --> col STATUS,PAYCHNL FYI, status refers to the paychnl method status:-==> 1 means the current paychnl method==> 2 means the previous paychnl method paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE. Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID. Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODEtable CHFF --> col STATUS,PAYCHNL SELECT DISTINCT PTFF.TRANSCODE,PTFF.ID,PTFF.TRANS_ID,PTFF.TRANSDATE,PTFF.EFFDATE, CHFF.STATUS,CHFF.PAYCHNL FROM DBO.PTFF PTFF JOIN DBO.CHFF CHFF ON CHFF.ID = PTFF.ID WHERE PTFF.TRANSDATE BETWEEN 130501 AND 130831 AND PTFF.TRANSCODE='T522' AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B'))) However, the script above returns :-1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2,2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID Samples of the result as below. ID STATUS TRANS_ID PAYCHNL TRANSDATE EFFDATE TRANSCODE 5188 1 712 CC 130829 20130920 T522 9361 1 64 CC 130816 20140813 T522 7853 1 153 CC 130820 20130814 T522 8949 1 51 CC 130812 20130801 T522 8949 2 51 B 130812 20130801 T522 1908 1 455 CC 130516 20131129 T522 1908 2 455 A 130516 20131129 T522 1908 1 409 CC 111019 20111129 T522 1908 2 409 A 111019 20111129 T522 1908 1 404 CC 110929 20111129 T522 1908 2 404 B 110929 20111129 T522

Hello guys, i need some experienced info here.. i need to sort selected data, thats why i used case like: SELECT * FROM Customers ORDER BY CASEWHEN CustomerName LIKE '%a%' THEN 1WHEN CustomerName LIKE '%b%' THEN 2ELSE 3END Everythings fine here, but I need to sort each sector (1, 2 and 3) seperately by CustomerName`s lenght. How to do that? any ideas ?

I have a nifty query that pulls data by orders.orderid > '85000' (and I update that number every week based on orders.) But I want it to automatically pull the most recent 3,000 orders instead of changing that number all the time, so I was hoping to set a variable which is Max(order.orderid)-3000, thus pulling the last 3000 order records. Here is my static select: SELECT Orders.OrderID, Orders.OrderStatus, Orders.CustomerID, Orders.ShippingMethodID, Orders.BillingFirstName, Orders.BillingLastName, Orders.BillingAddress1, Orders.BillingAddress2, Orders.BillingCity, Orders.BillingState, Orders.BillingPostalCode, Orders.PaymentAmount, Orders.OrderDate, Orders.ShipDate, Orders.LastModified, fraud.score, CAST(Orders.OrderID AS VARCHAR) AS 'za_orderid_full', CAST(Orders.CustomerID AS VARCHAR) AS 'za_custid_full'FROM Orders JOIN Fraud ON Fraud.OrderID = Orders.OrderIDWHERE Orders.ShipDate IS NULL AND Orders.OrderID > '85000' AND Orders.OrderStatus <> 'Cancelled' Can someone tell me how to declare and set that variable? Or suggest another way to accomplish this? Your help is greatly appreciated.

I have the following sql query in vb.net and ms access , how do I create a table from the query result ? SELECT 'Table1' AS [Table], SUM(a) - SUM( AS Result FROM table1 I have tried but it does not work create table tble10 as SELECT 'Table1' AS [Table], SUM(a) - SUM( AS Result FROM table1 any help would be appreciated

CREATE PROCEDURE

I created an asp.net webform that submits to an sql database on an sql server. The web server is Windows server 2012. The page successfully submits when I run the page using Visual Studio 2012, but when I run it on the web server (not using Visual Studios) I get the error message below when I click submit. It says login failed for user domainservername$, but the username in the connection string is user we created for the sql database. It uses Windows Authentication. I setup the web server for asp.net. I haven't done it before, so I assume the issue is there or in the connection string, though I'm not sure. The connection string is below. I'm new to asp.net, c#, and sql, so I'm learning as I go in this project. Let me know if you need anymore information. Thanks for any suggestions. <connectionStrings> <add name="MyConsString" connectionString="Server=sqlserver; Database=database; User Id=username; password=password; Trusted_Connection=Yes;" providerName="System.Data.SqlClient" /> </connectionStrings> Server Error in '/' Application. -------------------------------------------------------------------------------- Insert Error:Login failed for user 'domainservername$'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Exception: Insert Error:Login failed for user 'domainservername$'. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [Exception: Insert Error:Login failed for user 'domainservername$'.] Survey.WebForm1.ExecuteInsert(EventArgs e) in servernamec$inetpubSurvey2Surveydefault.aspx.cs:489 Survey.WebForm1.Button1_Click(Object sender, EventArgs e) in servernamec$inetpubSurvey2Surveydefault.aspx.cs:39 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3804

hello... I have written a stored procedure to get a column Named as "Name" from the table DN_Software.The query is "Select Name From DN_Software Where DN_DiscoverHostID=5" for Particular ID. Now i want to display that list of names in front end page. please help me how to display using that stored procedure using asp.net displaying in c#.please... "ALTER PROCEDURE [dbo].[DN_Software_Read_Distinct_Name_By_DiscoveredHostID]( @Active [int], @DN_DiscoveredHostID Int) AS SELECT DISTINCT Name FROM DN_Software where DN_DiscoveredHostID = @DN_DiscoveredHostID --EXEC DN_Software_Read_Distinct_Name_By_DiscoveredHostID @Active = 1, @DN_DiscoveredHostID = 5" --------------this my stored procedure.

I am wanting to write a procedure that will insert new/updated fields into another table Ex: tblStaff and tblUsers Fields in tblUsers needs to be inserted into tblStaff when new records or added or fields are updated. What is the best route to go to accomplish this?

I have a table called employee in which there's a field called birth_date. Iwant to get the output in the format of Jan-01-2013.My sql is $sql="SELECT emp_name,emp_address,salary,DATE_FORMAT(birth_date,'%b,%d,%Y') AS birth_date FROM employee";$query=mysql_query($sql,$connect); But the query is not succesful. What have I done wrong?Any help to correct this please

I am needing to change the data type in a table but it won't allow me to. I usually manually drop and recreate a table but was going to try and use SQL server template since the table is rather large (right click on table-> script table as-> drop and create to) Would you suggest using this template or just manually writing it myself and going from there? IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblStaff]') AND type in (N'U'))DROP TABLE [dbo].[tblStaff]GOUSE [Test]GO/****** Object: Table [dbo].[tblStaff] Script Date: 07/10/2013 12:39:35 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblStaff]( [StaffID] [bigint] NOT NULL, [LastName] [varchar](50) NULL, [FirstName] [varchar](50) NULL, [Status] [nvarchar](10) NULL, [FileNum] [int] NULL, [Supervisor] [nvarchar](150) NULL, [extension] [nvarchar](25) NULL, [JobID] [varchar](10) NOT NULL, [StartDate] [datetime] NULL, [EndDate] [datetime] NULL, [Note] [nvarchar](250) NULL, [Other] [nvarchar](150) NULL, [AddedBy] [nvarchar](50) NULL, [DateRecDeleted] [datetime] NULL, [DeletedBy] [nvarchar](50) NULL, [SSMA_TimeStamp] [timestamp] NOT NULL, [MachineName] [nvarchar](50) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO

I am wanting to insert fields from a table (data types are varchar(50) into another empty table (who's data type is nvarchar(50)). I believe I have created the correct query to achieve this but received an error message when executing "Msg 8152, Level 16, State 4, Line 1 String or binary data would be truncated" Would these two data types be the cause of this? I'm using some other data types (int, bigint) that could possibly be the issue but these are the main ones used to I wanted to see about them first.

I'm needing to insert values into a table. I have the data type set up as varchar(4). The values are combinations of 4 letters and/or numbers. When trying to insert it won't accepts the alpha characters. The column is set to Not Null and as the primary key. Any suggestions as to why it won't accept?

I was needing to change the data type for a field in a table that was the primary key. After researching the solution I came up with was to drop the table and recreate it. I dropped the table and then attempted to recreate it only it wouldn't take the original table name so I modified it and inserted the columns into the table under the correct data types. I believe in doing this I messed something up. When I access the site that the database is tied to I can't view information for the table I recreated or the main table it's connected to. I have tried to go to the affected tables and correct this issue by trying to change it to new table information but I receive an error. Now I am at a standstill and unsure of what needs to be done next. Thanks

I want to perform a join on three tables.Table 1: part_masterColumn: Item, Item_statusTable 2: Order_DetailsColumn: Item, order_num, order_typeTable 3: OrderColumn: order_num, order_typeHere is the cardinality:part_master <--> order_details (1..1)order <--> order_details (1..n)I want to perform a search on order table, to retrieve item_status from part_master. In other word, I want to see item_status, for an order, using order_num as a search criteria.I tries something like this, however, it throws a exception:select part_master.item_status from part_master where item = (select item from order_details where order_details.order_num = order.order_num)The error says, the second 'select' statement returns multiple results which is not correct..Help....!

I would like to create a stored routine for MySQL that figures out the number of business or working days for a month (Working Days are Monday thru Friday). I have not created the procedure portion of it yet as I want to ensure that the code works first. Please note that there is a function called first_day. This is a function provided by my hosting provider. I also have another function for this that will figure out the first day of the month if I didn't have it from the hosting company. It is not currently used and can be removed for testing purposes. I am testing this through phpmyadmin SQL Query. phpmyadmin uses the mysql extension and not the mysqli extension processing if that makes a difference. (The hosting company refuses to update it). It's a syntax error however I don't know what the syntax error is. All it tells me is: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE(@daycount < @totaldays) DO IF (WEEKDAY(@checkweekday) < 6) THEN ' at line 2 My Syntax Error is in the following: WHILE(@daycount < @totaldays) DO IF (WEEKDAY(@checkweekday) < 6) THEN My Code: SELECT MONTH(CURDATE()) INTO @curmonth; SELECT MONTHNAME(CURDATE()) INTO @curmonthname; SELECT DAY(LAST_DAY(CURDATE())) INTO @totaldays; SELECT FIRST_DAY(CURDATE()) INTO @checkweekday; SELECT DAY(@checkweekday) INTO @checkday; SET @daycount = 0; SET @workdays = 0; BEGIN WHILE(@daycount < @totaldays) DO IF (WEEKDAY(@checkweekday) < 6) THEN SET @workdays = @workdays+1; END IF; SET @daycount = @daycount+1; SELECT ADDDATE('@checkweekday', INTERVAL 1 DAY) INTO @checkweekday; END WHILE; END; SELECT @workdays; I receive the same error with the following bit of code so it probably has something to do with this: SET @workdays = 0; IF (WEEKDAY('2013-06-13') < 6) THEN SET @workdays = @workdays+1; END IF; SELECT @workdays; Is someone able to assist?

am searching the code for making pdf in asp classic, i have made a form in asp and on submit button i want that it get convert in pdf and get to attached in email. thanks in advance plz help

Friends, i have 3 tables.....one named stocklist, other one....partyinsrcd, 3rd one.....sregisteri would like to select distinct vehicle model name from table stocklist whose related chassis numbers not available in the other two tables.......please help...Is this the sql for the above....pl check. i am not getting the result from the below sql......SELECT distinct stocklist.vehicle FROM (stocklist inner JOIN sregister ON stocklist.chassissregister.CHASSIS) inner JOIN partyinsrcd ON stocklist.chassisright(partyinsrcd.chassisno,7) WHERE (((sregister.CHASSIS)stocklist.chassis) and (right(partyinsrcd.chassisno,7)stocklist.chassis)