Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 10
Table Red 20
Table Yellow 30
Chair Blue 40
Chair Red 50
I'm wondering if there is a group state like this:
Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item
which returns this:
I am trying to use SQL to pull unique records from a large table. The table consists of people with in and out dates. Some people have duplicate entries with the same IN and OUT dates, others have duplicate IN dates but sometimes are missing an OUT date, and some don’t have an IN date but have an OUT date.
What I need to do is pull a report of all Unique Names with Unique IN and OUT dates (and not pull duplicate IN and OUT dates based on the Name).
I have tried 2 statements:
#1: SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#)) GROUP BY tblTable1.Name, tblTable1.INDate ORDER BY tblTable1.Name;
#2: SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#)) UNION SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#));
Both of these work great… until I the OUT date. Once it starts to pull the outdate, it also pulls all those who have a duplicate IN date but the OUT date is missing.
Example:
NameINOUT John Smith1/1/20141/2/2014 John Smith1/1/2014(blank)
I am very new to SQL and I am pretty sure I am missing something very simple… Is there a statement that can filter to ensure no duplicates appear on the query?
This is not really a question, maybe a start of discussion if this could be a feature in coming versions...
Let's say I have a table like this: id text1 text2 int varchar varchar
1 abc ghi 2 abc ghi 3 def ghi
and I want to query using group by, I would write SELECT text1, text2 FROM table GROUP BY text1, text2
This is not too bad, but imagine I got several columns of text I need to query, then I need to put them in both places, SELECT and GROUP BY. If I do use functions like SUBSTRING or concatenation on the columns, I need to repeat these in the GROUP BY as well.
It would be nice if I could say "GROUP BY id", and since id is unique on the table it is obvious that all columns taken from this table are already grouped and can be queried without having to have them in group as well. For functions this is of course only possible if all parts only refer to columns from this table, or absolute entries, like LEFT(varchar ,3) or int+1 or such.
With this example, the benefit is not visible: if I added the id to the GROUP BY, I get every single row of course. GROUP on unique is useless... It does make sense if you query another table and join in this one as a lookup, and the id gets "multiple" this way.
As I said, just an idea to make the GROUP BY section more transparent and avoid repeating sections!
I created an ouput text file that was perfect until specs were revised today to add quarterly tax and deduction summations for each employee. (Expected turnaround time for new spec: today!)
So my employee record now has 8 records (which I expected) because I had to join it to the pay_summary table. Like this:
SELECT COALESCE(CONVERT(char(1),e.record_status),'') + COALESCE(CONVERT(char(4),'97 '),'') and many more fields from employees as e JOIN emp_taxes as t on e.employee_no = t.employee_no LEFT OUTER JOIN pay_summary AS p ON e.employee_no = p.employee_no
Where dateadd(d, 0, datediff(d, 0, p.dated)) BETWEEN '20061231' AND '20070401'
and e.record_status not like 'D' and t.tax_authority_type = 'F' and e.company_no = '2' and e.employee_no = t.employee_no
order by e.employee_no
There are 6 or so fields I intend to sum as records to go into the file. In another language, in order to get one record per employee_no, the syntax would be like this:
sort on employee_no footing at employee_no report field_a field_b field_c subtotal field_e field_f subtotal
in which case fields c and f are numeric and summed from the multiple pay_summary records.
What is the (most efficient) SQL command to give only one record per employee while summing all the detail records?
I want the sum of this to be Client 1 + Client 2 + Client 3 + Client 4 which is $100. But if I just do a sum it adds up each row. I also have them grouped together, so when it's shown, I only seen one of each item, but a sum still grabs all the duplicates.How can I go about getting the unique sum
I developed a main report containing numerous subreports. But now I'm trying to page break on these subreports and because these subreports all have sub-sub reports, I get this error when I try to run this report..The value '22' is invalid. Valid values are between '0' and '1'. (rrRenderingError)..I am grouping on uniqueidentifers and I do not get errors on subreports that have sub-subreports. How can I avoid this error and get these subreports to page break? (I don't get any errors if I remove the page breaks).
What would be the best way to insert unique values into a table/unique column ? I cannot make that table/unique column as indentity. Right now, I use a staging table with indentity column, insert rows then insert rows back to final table.
Hi,I have two tables such that in each table I need to make sure thatcolumn x in table A and column y in table B have a unique valuesmeaning that a user cannot insert a value to column A if its alreadyexist in column B and vice versa.How can I enforce it? Please remember that this two different tables.Thanks,Ori.
Hi, I have been asked to write some code that can check a large table for duplicate values in a non pk column. The table may have up to 1000000 rows. The PK column is an auto increment field. For performance reasons the column in question could not be set to unique values only for inserts, an algorithm is used to create unique no's before the insert but what I am doing is double checking that their have been no duplicates created accidently. If their are duplicates I need to know what rows they occurred on.
I have a list of zones and IP addresses assigned to those zones. On the page that manages those zones and their IP addresses needs to only display the RANGES of IP addresses that exist for that zone. For example if this is my data for Zone 1:IPAddress (removed first octet)002.202.148002.202.149187.202.243187.202.244187.202.245187.202.246187.202.247187.202.248187.203.187I want to display it as this on the management page:Zone 1 : IPAddress Ranges From 2.202.148 to 2.202.149 From 187.202.243 to 187.202.248 From 187.203.187 to 187.203.187Is there any way to make the SQL statement list the mins and maxes for every range? Or is this something I just HAVE to do in the code? I would like a record to return:MIN | MAX2.202.148 | 2.202.149187.202.243 | 187.202.248187.203.187 | 187.203.187I think I'm trying to expect a lot from SQL but I thought i would ask anyway. Thanks!~Cattrah~
I need a query to return two values. One will be the total units and the other will be total unique units. See exmaple data below. It does not have to be one query. This will be in SP, so I can keep it seperate if I have to.
Total Units = 7 - easy to do by using count() Total unique units = 4 - I cannot use group by as it would return multiple results for each unit, which is not what we want.
I need a bit of help with a join. I have 2 tables :
TradeSummary has fields : SymbolID, CurrentPrice, TotalValue
Trades has fields : SymbolID, TradeID, ExecutionTime, TradeValue
TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID
and what I want to retreive is :
For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary and also get TradeValue from Trades for the record for max(ExecutionTime) tables are joined on TradeSummary.SymbolID = Trades.SymbolID
Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID
I am using Access 2007 and I have 10 columns of data.
I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.
But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.
I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.
I know that I am missing something basic but well, I can't figure it out.
Hello all. I've been pulling my hair out for the last few weeks trying to come up with a statement that will do what I want. I'm hoping someone can lend some help.
Basically I have a table of articles with titles. I want to go thru the titles and find out what words show up the most. For example, if I had these two article titles in two records:
Microsoft develops new software for NASA NASA blames software problem on Microsoft
I would get the following results - the word and the number of times it appears:
Microsoft 2 NASA 2 Software 2
The statement should ignore those words that only appear once. It would be nice to skip static words like the, and, a, etc.. (Or words 3 characters or less)
I have fulltext query enabled on the table which works great for searching, but not for what I want it to do.
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
Hi I am using VS 2005 with SQL 2005. I had a Datagrid and i Need to bind data into it. I need to show Random Unique ID's based on the Parameter i pass. I wrote select top 5 * from Employee order by NEWID() for this i had created a Stored Procedure as create Proc Demo(@N int)as beginselect top @N * from Employee order by NEWID()end Its showing a syntax error near @N. How can i pass the parameter for Select Top @N * from .......
I am in a situation where I need to find out unique field names with different values in a table having 200+ columns. Let's say I have two rows with 200+ columns ( I exported these rows from Lotus Notes to SQL Server) I am not sure what columns makes unique of these rows. It's going to be tedious by checking each column values. Is there anyway I can write a squl query on these two rows which outputs column names which are having unique values. I would appreciate If anybody gives me hint about achieving desired result
I have this query and it works except for I am getting duplicate primary keys with unique column value. I want to combine them so that I have one primary key, but keep all the columns. Example:
Key column 1 column 2 column 3 column 4 A 1 1 A 2 2 B 2 3 B 5 5
it should look like:
A 1 1 2 2 B 2 3 5 5
Here is my query:
SELECT * FROM [TLC Inventory].dbo.['2014 new$'] WHERE [TLC Inventory].dbo.['2014 new$'].mis_key LIKE '2%' AND dbo_Product_Info#description NOT LIKE 'NR%' AND dbo_Line_Info#description NOT LIKE 'OBSOLETE%'
I have a table which maps two related IDs. That table has 3 columns: ID, BHID & EPID. I need to find all of the BHIDs where the EPID is unique. It seems easy enough, but I keep going in circles..
USE [CGB] GO /****** Object: Table [dbo].[ePID_BHID] Script Date: 04/15/2015 15:48:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
I am trying to insert values into a table that doesn't exist there yet from another table, my problem is that because it is joined to the other table it keeps on selecting more values that i don't want.
Code Snippet
SET NOCOUNT ON
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID) SELECT M.MemberID, '6', CASE M.MaritalStatusID WHEN 1 THEN '7'
WHEN 2 THEN '8'
WHEN 3 THEN '9'
WHEN 4 THEN '10'
END
FROM Members M
INNER JOIN _MemberProfileLookupValues ML
ON M.MemberID = ML.MemberID
WHERE M.Active = 1
AND OptionID <> 6
When i execute that code it returns all the values, let say OptionID = 3 is smoking already exists in the MemberProfileLookupValues table then it is going to select that persons memberID
I want to insert only members values that aren't already in the _MemberProfileLookupValues from the Members table (I think that it is because of the join statement that is in my code, but i don't know how i am going to select members that aren't in the table, because i have a few other queries that are very similar that are inserting different values, so ultimately
ONLY INSERT THE MemberID the values 6 and the statusID of X if it is not in the table already.
Any ideas / help will be greatly appreciated. Please help.
I'm trying to use merge data from a staging table to a production table. There are a lot of duplicate values for serverName and I only want to insert one instance where there are duplicates.
How I can adapt the code I have so far to achieve this?
MERGE tblServer AS TARGET USING tblTemp AS SOURCE ON (TARGET.serverName = SOURCE.serverName)
WHEN MATCHED THEN UPDATE SET TARGET.serverName = SOURCE.serverName, TARGET.serverLocation = SOURCE.serverLocation
WHEN NOT MATCHED BY TARGET THEN INSERT (serverName, serverLocation) VALUES (SOURCE.serverName, SOURCE.serverLocation)
I've been searching and trying out SQL statements for hours and I'mstill having a problem.I'm querying 3 tables... and I only want the first unique recordreturned.Currently, the data returned looks like this..............New York New York ANew York New York BNew York New York CLos Angeles California ALos Angeles California BLos Angeles California CI want the select statement to return this instead...New York New York ALos Angeles California AI'm using MS SQL server.please help?thanks for your help.
I have a complicated problem, and I'm new to SQL so any help would be greatly appreciated.
I am creating an export file (fixed width) that contains a breakdown of items on an invoice, and each "export file" can contain many invoices. The problem is that I need to apply an incremental "invoice" count on each line. This isn't as simple as doing a running sum of "1" on each record, because the first 5 rows may all be on the same invoice, and all rows need to be identified as being associated with "invoice 1". The next invoice will be known as "invoice 2" and again may contain many rows, all requiring "invoice 2".
Does this make sense?
EG.: I am shipping products, and the breakdown is: Vessel, Voyage, Invoice No, Product, Mark.....
SAGMIR 025 001 HEM/FIR HLF550...
SAGMIR 025 001 HEM/FIR KILN-D HLF505...
SAGMIR 025 002 HEM/FIR HLF660....
The SQL statement that produces the above is a Select query with a grouping on VES/VOY/BL_ID/PRO/MARK where the "BL_ID" indicates they are on the same invoice, but is not the incremental number I require. Complicated, i know...
Thanks in advance for anyone who can help.....if this explanation isn't clear please tell me!
After adding a Unique constraint to a database I cannot add more than one record with a null value for the constrained field. I've tried both adding the constraint to an empty table as well as a table with multiple null values already in the subject field; both efforts have failed.
According to BOL SQL-7 allows Unique Constraints on fields with Null values. Am I missing a step? I do need to allow nulls in the field yet ensure that when there is a non-null value it is unique.
The SQL statement I've used is: ALTER TABLE tbl_MasterUIC ADD CONSTRAINT uniquesamplenbr UNIQUE NONCLUSTERED (samplenbr)
Create table #test ( id int primary key, Name varchar(100) ) insert into #test values (1,'John') insert into #test values (2,'Walker')
[Code] ....
-- Query 1 : update #test set name = 'Joney' where id = 1
-- Query 2 : set rowcount 1 update #test set name = 'Joney' where id = 1 set rowcount 0
1. #test table have primary key & clustered index. 2. Obviously only one row will be available for an id. 3. In query 1, will the sql server look for matching rows even after it found 1 row? 4. Will query 2 really gains some performance?
I have a question about adding a unique key column to an existing table.
what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?
I've got a large table (3mil records) with a number of columns, but currently no way to refer to any individual column. I therefore need a primary key, but does anyone know of a SQL statement I can use that will create a column (say, ID) that is automatically filled with an incrementing 'counter'? Or, instead, how can I set unique incremental values after first creating the column?
I need to write a select query that gets the values of all columns but only returns unique sites because some sites are load balanced across several servers and where this is the case I don't want the site to appear multiple times in the list.
Below is my sample data. I can't figure out how to select Unique phonenumber contacts for the same Ranked values from the set.
Basically the table is a mix of contactIDs. Some of them have duplicate phone numbers and through a separate mechanism we have ranked them.
It's easier then to pull out max(ranked) CLI_Numbers and their counterpart contactID(s). But I am also getting 2 or more records where the rank happens to be the same. I don't want that. Any one of the contactID will do for me.
The table has also same cliNumbers with different rank values, which are then correctly being picked up in the query below.
Note: ContactId is a unique value for each person in the table. RecordID is simply RowID.
( I have attempted to populate a sample data suited for this forum - not sure how it comes out on the browser)
if object_id('tempdb..#MyData') is not null drop table #MyData create table #MyData ( RecordID int, contactID int, forename varchar(25), surname varchar(25),
[Code] ....
This is my query attempt
With RankedmobileDuplicateSet as( select cliNumber, max(ranked_value) as ranked_max_value from #temp_UK_mobiledata group by cliNumber)
