SELECT With GROUP BY And Build A List From Vales Not Shown
Apr 16, 2004
Hard to write a subject line to describe this one.
Anayway, I have a table with names and address plus an extra field noting a part number of product.
I'd like to build a SELECT string that will return one result for each name/address (uniques only in other words) and build a comma delimited field of all the part numbers for that name/address.
Example:
NAME ADDRESS PART NUMBER
John Smith 555 Main st., los angeles, ca 90003 5000
John Smith 555 Main st., los angeles, ca 90003 6650
Mike Jones 8569 West 18th Ave., San Diego, ca 1255
John Smith 555 Main st., los angeles, ca 90003 5144
Mike Jones 8569 West 18th Ave., San Diego, ca 2399
So I'd like my results to look like this:
NAME ADDRESS PARTS
John Smith 555 Main st., los angeles, ca 90003 5000,6650,5144
Mike Jones 8569 West 18th Ave., San Diego, ca 1255,2399
I am using SQL Server 2005 and fairly new at using SQL Server. I am having problems using a Case statements in the select list while have a group by line. The SQL will parse successfully but when I try to execute the statement I get the following error twice :
Column 'dbo.REDEMPTIONHISTORY.QUANTITY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Below is the my sql statement:
SELECT dbo.DateOnly(TH.TransactionDate) AS RptDate, RH.Item,
ItemRef =
Case
when RH.Quantity < 0 then Sum(RH.Quantity)
when RH.Quantity >= 0 then Sum(0)
end
FROM dbo.RHISTORY AS RH INNER JOIN dbo.TRANSHISTORY AS TH ON RH.TRANSACTIONID = TH.TransactionID
WHERE (dbo.DateOnly(TH.TransactionDate) BETWEEN '10-1-2007' AND '10-5-2007') AND (RH.TransactionCode IN (13, 14, 15, 16))
Group by dbo.DateOnly(TH.TransactionDate), RH.Item
The TransHistory table contains, primary key transactionid, TransactionDate and the RHistory contains all the details about the transaction, the RHistory table is joined to the TransHistory table by foreign key TransactionID. I am trying to get totals for same item on the same day.
Any help will be greatly appreciated. I am also having trouble using If..Then statements in a select list and can not fin the correct syntax to use for that.
Hi all - i'm trying to put together my first .Net web page (have switched from Dreamweaver to VWD - VWD keeps swapping my tab-indents for spaces, and none of the options stop it!).Here's a table that i'm trying to query: ItemID | ReviewRating | ReviewRatingOutOfAs i'm sure you've guessed, it's a reviews table, where there can be several records with the same ItemID and different (or the same) ReviewRating and ReviewRatingOutOf's. As the reviews are collected from lots of sources, the ReviewRatingOutOf will change (one review might be 3/5, while the next, for the same ItemID, could be 8/10, etc). Now, what i'm trying to do is return a list of ItemID's ordered by their RATIO (which is the sum of each ItemID's ReviewRating's divided by the sum of each ItemID's ReviewRatingsOutOf's - in other words, average score). My first guess was this:"SELECT DISTINCT ItemID FROM Reviews ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" - unfortunately that doesn't work (problems with the SUM aggregate functions, and overflow errors, whatever they are). Now, this string works: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)" - right now, that just adds up the ReviewRatings, so an item with 10 reviews that only got awarded 1/5, 1/10, 1/8, etc (all 1's, therefore achieving a combined ReviewRating of 10 out of a very much higher ReviewRatingOutOf), would appear higher than an item with 1 review that got 5/5. Making the string into this: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" (which is what I need), unfortunately gives me errors...Anyone have any ideas? Is there possibly a way to simply read all the distinct ItemID's with SQL, then get the two SUM's for each ItemID, then calculate the ratio of the two SUM's, and stick the ItemID's and the ratio into some sort of array, and have C# order the array for me, based on the ratio? I'd appreciate an example of that if possible, as i'm a complete C# beginner :-)Thanks in advance!
I have data that is grouped by a code number. One of the code numbers have over 600 rows, while other code numbers have around 10 to 20 rows within it.
When I run the report the code number that has over 600 rows gets split over 2 pages while the other code numbers each get their own page.
How do I make it so that when I run the report the code number that has over 600 rows gets all displayed on the 1 page instead of being split over 2?
Not sure if this is the place to post this, but here goes.
I need to setup an options screen where my customers can customize which locations will be stored for their user id when pulling reports. I have checkbox list that dynamically loads their locations. I need to store the selected checkbox items in my table and then each time they login in to run a report, it will use the stored Location values in my SQL query.
Synopsis: Selected locations stored in table. When the report is ran, the location values are pulled and added to my queries WHERE clause.
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns." How can I do this?
Insert Query: insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE) SELECT DISTINCT tri_IDENT.IDA AS PPID, tri_Ldg_Tran.CLM_ID AS PTicketNum, tri_ClaimChg.Line_No AS PLineNum, tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT' THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate, tri_Ldg_Tran.Tran_Date AS PEntryDate, tri_ClaimChg.Hsp_Code AS PHCPCCode, tri_Ldg_Tran.Adj_Type, tri_Ldg_Tran.PRS_ID, tri_Ldg_Tran.Create_Time, tri_Ldg_Tran.Adj_Group, tri_Ldg_Tran.Payer_ID, tri_Ldg_Tran.TRN_ID, tri_ClaimChg.Primary_Claim, tri_IDENT.Version FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc = qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN tri_Ldg_Tran RIGHT OUTER JOIN tri_IDENT LEFT OUTER JOIN tri_ClaimChg ON tri_IDENT.Pat_Id1 = tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID = tri_ClaimChg.PRS_ID AND tri_Ldg_Tran.Chg_TRN_ID = tri_ClaimChg.Chg_TRN_ID AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN tri_Payer ON tri_Ldg_Tran.Payer_ID = tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type = tri_Ldg_Tran.Pay_Type AND qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg') AND (tln_PaymentTypeMappings.PTMClientCode = 'SR') AND (tri_ClaimChg.Primary_Claim = 1) AND (tri_IDENT.Version = 0)
Hi there, I have 3 columns that contain the same kind of information and I would like to do a select that would return all the distinct records of all 3 colums. Any help woulb be appreciated. Thanks
I am trying to allow a user the ability to search a database and select from various fields to search, such as Keywords and Filename. I tried building something like this: SELECT filenameFROM pictableWHERE (@searchby LIKE @searchwords) It allows me to enter the two varables (keywords and test), but returns no rows. If I simply replace @searchby with 'keywords' (ensuring no spelling errors), then I get a return of one row. So this works: SELECT filenameFROM pictableWHERE (keywords LIKE @searchwords) Can someone tell me what is going on? I have tried all sorts of quotes and parens to no avail. Thanks in advance.
This "Total" is of All States(AZ, CA, ID, NV, WA, OR). But I Just want the total of just four states i.e(ID, NV, WA, OR). And I also Want that States which are not included in the total i.e(AZ, CA) Should also appears in the matrix , but their values should not include in the total. I have already applied the filter on the subtotal, but (AZ, CA) did'nt appears in the matixr after filter.
The below code is ASP 1.0 to dynamically search a database and I want to use the same concept for a ASP.Net 2.0 solution. Do I do this in the code behind or on the aspx page and if on the aspx page what controls do I use for the array split? Basically where do I start. It took me a long time to get this old code working, I am hoping it is simpler in 2.0. Thank you OLD ASP 1.0 code to dynamically build a Sql Select statement for searching a database using one or more search words entered by user. If Request.Querystring("kwdSearch") <> "" ThenDim kwdString, ArrKwdString, iCountiCount = 0 kwdString = Replace(Request.Querystring("kwdSearch"), "'", "''")ArrKwdString = Trim(kwdString)ArrKwdString = Split(kwdString, " ",-1,1) For iCount = 0 to UBound(ArrKwdString) If iCount < UBound(ArrKwdString) Then Criteria = Criteria & "tblLinkInfo.L_Keywords LIKE '%" & ArrKwdString(iCount) & "%' AND " Else Criteria = Criteria & "tblLinkInfo.L_Keywords LIKE '%" & ArrKwdString(iCount) & "%' " End ifNext RS.Open "SELECT * FROM tblLinkInfo Where (" & Criteria & ") AND L_Enabled = 1 ORDER BY " & SortBy & "L_Rank", CNN, 3 If RS.EOF Then If Rs.State Then RS.Close RS.Open "SELECT * FROM tblLinkInfo WHERE L_Description LIKE '%" & Replace(Request.Querystring("kwdSearch"),"''","'") & "%' AND L_Enabled = 1 ORDER BY " & SortBy & "L_Rank", CNN, 3 End If RESULTS --- Display results with Repeater1.DataBind(); etc Exit SubEnd If
I have a department table like this:DeptID Department ParentID, Lineage1 HR NULL (2 Temp1 1 (1,3 Temp2 2 (1,24 PC NULL (I have a deptmember table like this:DeptID MemberID IsManager1 1 Y4 1 YI need to query table to get all department belong to MemberID 1 withall children departments.My thought is:1. Do Select * from deptmember where MemberID=1 and IsManager=Y2. Loop thru this table to build SQLWhere Lineage like '%1' OR Lineage like '%4'3. Select * from department using where statement from step 2.How do you loop thru results from step1, Do I need to use a cursor?Thanks,HL
Hello,So my table contains say 100,000 records, and I need to group thecategories in fld1 by the highest count of subcategories. Say fld1contains categories A, B, C, D, E.All of these categories contain subcategories AA, AB, AC, AD,...AJ, BA,BB...BJ, CA, CB, CC...CJ, etc in fld2.I am counting how many subcategories are listed for each category. LikeA may contain 5 of AA, 7 of AB, 3 of AC, 11 of AD...1 for the rest and20 of AJ. B may contain 2 of BA, 11 of BB, 7 of BC, and 1 for the rest.I want to pick up the top 3 subcategory counts for each category. Wouldlook like this:Cat SubCat CountA AJ 20A AD 11A AB 7B BB 11B BC 7B BA 2So event though each category contains 10 subcategories, I only want tolist the top 3 categories with the highest counts as above. If I justdo a group by and sort I can get this:Cat SubCat CountA ... ...AAAAAA...B ... ...BBBBB...But I just want the top 3 of each category. The only way I can think ofto do this is to query each category individually and Select Top 3, andthen Union these guys into one query. The problem is that I have tohardcode each category in the Union query. There may be new categoristhat I miss. Is there a way to achieve what I want without using Union?Thanks,Rich*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I need to export a list of name and email address fields from a SQLtable, in some format so that a user can take the file I generate andImport it into Outlook and it will keep all the names together in aGroup or Distribution List rather than dumping them into the user'smain address book. Any ideas?Francesco
we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..
C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild
Through build process we get a error:
External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):
We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
When I am trying to insert to data from SQL ssis package to SharePoint list people or group column I am getting below error.[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.ย The ProcessInput method on component "SharePoint List Destination" (25) failed with error code 0x80131500 while processing input "Component Input" (34). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
I have done queries before where you have something like this: Select name From people Where uid in (Select UID from employees where salary > 500000) as an example. I have to use stored procedures, I can't connect directly... I have a datatable of records that I select from a database. In the database table there is a flag 'checkedOut' (bit field) wich I want to set to true (1) for each of these records. Since I have to use stored procedures I think I am limited to passing in parameters. I wrote a function using a stringbuilder to concatonate the key fields into a comma delimited list "1, 55, 98" etc. and thought to use it as the clause in the 'in' sample ie:Update ServiceRequests set CheckedOut = 1 Where UID in (@UIDList)
I have tried a few different things, none works. Any ideas how you might accomplish this with stored procedures? The records are passed to a thread to be dealt with and it's possible the next time it runs it will pick up the same records for processing if the first thread isn't through. I needed the 'CheckedOut' to ensure I don't accidentally do that. Thanks.
Hi all. I need to create a select query in my program that will select from a list of values that are stored in a dataset. Let see this example: selectcmd = "Select * from mytable where myfile =" ??????? cmd = New SqlCommand(selectCmd, da.SelectCommand.Connection)
The values I need to put on ????? are stored in a dataset. For example if the dataset is populated with the following values: A B C D E
I would like to build a query like that: selectcmd = "Select * from mytable where myfile = โAโ or โBโ or โCโ or โDโ or โEโ โ
I need to create a stored procedure that takes a list of product numbers(541, 456, CE6050,...) and selects only the customers that have all product numbers in the list. I have a product table that has the following schema:
So a customer can have more than one product, but I need a select statement that takes the product numbers and selects them against the product table and returns only the customerNumbers that have the entire list of product numbers. I have tried doing a join on the product list and productNumber, then join with the customer table on customerNumber, but that gets any entires found in the list not the entries that have all the product numbers. Thanks in advance for any help.
Hi world,Normally we receive the results of a query in several or thousands of rows. Select * From Clients-------------------------Row1. Client1Row2. Client2....Which is the the way to have everything on the same row separated by commas?Row1. Client1, Client2...thxDavid
I have a table that has a list of skills, ie "HP One", "HP Two"
I need to pass the these skills from my applications search page to a stored procedure, hence I have a snippet of the SP below.
DECLARE @SkillSet NVarChar (200) SET @SkillSet = '-HP One-,-HP Two-' SET @SkillSet = Replace(@SkillSet, '-', '''') SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN (@SkillSet)
The following sp does not return any results, but when i set the last line to ..
SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN ('HP One', 'HP Two')
It returns a set of results. Also when I do a select on the variable @SkillSet, ie SELECT @SkillSet, it displays 'HP One', 'HP Two'
Can enybody help me here, i Know i'm doing something wrong, but I cant think of what it is.
Hi, i have a big problem. Iยดm having trouble with the select statement in SQL query language. The problem is that I need to retrieve various data from database and the input object is a list. The simple way of doing this is:SELECT <return values>FROM <datatable name>WHERE (<select data parameter>)My problem is that my where parameter needs to be an array list. The simple way of solving the problem would be using a for statement in my c# code and call my store procedure various times, but my array list can be too long and take a long time to connect and search data for each statement, so I need to access the database once.Can anybody help; I would appreciate it very much thx, Malcolm
Hi, I would like to create a Stored Procedure for obtain a list of insert using the information of a select queryI have to do X insert for days, using my start selectConstant : start dayx insert for each day if in my select I obtain 550 records and x = 100I've to do 100 insert for day with information of the selectplease note that in the final day I have to insert only 50 records and not 100 (500 and 100 are not mutiples) Thanks
I have a dropdown list that is populated by two columns in a database.select (firstname+surname) AS Fullname from table where id = 'id'; It works fine but i want to know how i would get a space between the firstname and the surname because at the moment all the values come back as JoeBloggs....without any spaces
I would like to write a select statement where I specify a list of values in the 'Select' line, and would like the output to have one line for each element.
I tried using Case with no success.
For example:
Select a.id, a.timestamp, ('rowA','rowB') as 'Tag' from tableOne a where a.id = '1'
So the 'where' line would produce one row, however, the overall statement would produce two.
ID TimeStamp Tag -------------------------------- 1 2012-12-12 rowA 1 2012-12-12 rowB
The following returns all base tables within the database of type "varchar":
Code: SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM mydb.information_schema.columns WHERE TABLE_SCHEMA = 'master' AND TABLE_CATALOG = 'mydb' AND DATA_TYPE IN('varchar')" AND TABLE_NAME IN( SELECT TABLE_NAME FROM mydb.information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'mydb' AND TABLE_SCHEMA = 'master')
What I then want to do is... For each of these results:
Code: select [COLUMN_NAME] from [TABLE_SCHEMA].[TABLE_NAME] WHERE ID = 'test'
Is it possible to do this in one SQL command? Or do I manually have to do it for each in the list from my first query?
I have a table with DiscNo, Artist, Title and other fields. I would like to find all duplicate records with the same artist/title and with the first 3 characters of the discnumber. e.g.
SELECT Artist, Title Into #TempArtistTitle FROM MediaFile GROUP BY Artist, Title HAVING COUNT(SubString(DiscNo, 0, 3)) > 1
SELECT MediaFile.DiscNo, MediaFile.Artist, MediaFile.Title FROM MediaFile RIGHT OUTER JOIN #TempArtistTitle ON MediaFile.Title = #TempArtistTitle.Title AND MediaFile.Artist = #TempArtistTitle.Artist ORDER BY Artist, Title, DiscNo
Drop TABLE #TempArtistTitle GO
See, if the first 3 characters of the disc number is the same, it is the same manufacturer. This query works somewhat, although it returns records that the discnumber is unique too. Like below, the LG disc number shouldn't be returned, as there is only one record for that artist/title.
SC8151-10 - Garth Brooks - Friends In Low Places SC8125-04 - Garth Brooks - Friends In Low Places LG5003-07 - Garth Brooks - Friends In Low Places
