DISTINCT And Number Of Records
Mar 19, 2008
Wondering if there is a way to do this...
I want to select the DISTINCT user name from each record in a table, and then have another field tell me how many records it found..
is there any way to do this with SQL?
SELECT DISTINCT user_Name FROM table ORDER BY user_Name
is what I have now....
need something that will return:
user_Name numRecs
bob 3
fred 6
sam 1
linda 2
View 1 Replies
ADVERTISEMENT
Mar 29, 2007
suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"
I want's some thing like
field1 rowcount
aaa 5
bbb 6
View 1 Replies
View Related
Feb 19, 2013
I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.
View 8 Replies
View Related
Nov 17, 2013
I have a table name farmer data and it has attributes like farmer name, father name, pesticides used, town etc (these attributes would be required for query). also the data type of columns is var char.
I have to write a query to calculate the average no of farmers in each town.
How can I write this query? I have tried this query but its not giving me the count of all the farmers in each town. its not giving me the count of distinct farmers
select town, count (*)
from farmer_data
group by town
View 7 Replies
View Related
May 3, 2006
Hi, I have a table that for ease has this data in:R1, R2, R....z---------------------A | 12A | 22A | 30B | 0B | -1B | -3C | 100I want to generate a table for each distinct row in R1, gives a countof all the rows with data correspondingFor the above table I would getA | 3B | 3C | 1Im probably being stupid but cannot see this at the moment... pleasehelp.Thanks
View 3 Replies
View Related
Feb 12, 2015
select SERIALNO, Max(TIME)
from dbo.TASK A
join dbo.Status B
on A.TID=B.TID
where A.ID in ('1111',2222')
and A.TYPE='Pen'
group by B.SERIAL_NO, B.TIME
order by BM.TIME
For this query I may get serial no duplicates but times are unique
For that serial no, I have to find the recent time. But if I use group by, I am getting the wrong no
View 2 Replies
View Related
Aug 26, 2015
I have table A(year int, month int, user varchar(20)), and I am trying to write a view that would show number of distinct users in the last 3 months, last 6 months, last 9 months and last 12 months(all from the most recent year/month) in following format:
3 months 6 months 9 months 12 months
number of distinct users x y z w
View 20 Replies
View Related
Jan 7, 2008
How do I get distinct TitleID and Titles? Right now I'm still getting duplicates on them both. Here's my stored procedure.
select Distinct (Titles.Titleid), Titles.Title as TITLE, classifications.[description]as TOPIC,Titles.descriptions,media.[description] as MEDIA
from Titlesjoin resources on resources.Titleid = Titles.Titleidjoin media on media.mediaid = resources.mediaidjoin titleclassification on titleclassification.titleid = titles.titleidjoin classifications on classifications.classificationid = titleclassification.classificationid WHERE Title LIKE 'p' + '%' GROUP BY Titles.titleid, titles.title,classifications.[description],Titles.[descriptions],media.[description]
Thanks!
View 18 Replies
View Related
May 11, 2008
hi,
i want to ask a simple question,
i have a sql server relational tables named "tbl_Contact" (w/c has a field of ID & ContactName) and "tbl_reviews" (w/c has ID,ContactCode & Reviews).
the tables relationship is one-to-many, now my question is, how can i display a single record per 'ContactCode' from 'tbl_review'??
thanks..
View 7 Replies
View Related
Oct 1, 2007
I am trying to write a query that will only return the non distinct records
what i have is a bunch of line items that have an order id and a rate
there may be 10 line items with the same orderid, I need to get the orderid and rates for the orderids that have more than one rate
how would i do this?
View 15 Replies
View Related
Oct 16, 2007
how do i get an entire record returned when looking for only column to be unique?
basically i have 3 columns
id, tranid, appliedtranid
tranid can have multiple appliedtranids
i need to grab the top 10 distinct tranids but i need the entire record
View 4 Replies
View Related
Jan 31, 2008
hi I am trying to write a query that will return the most recent status of an event
my query is below
SELECT DISTINCT eventname, MIN(Status) AS Expr1, MIN(RecordUpdated) AS Expr2
FROM EventQueue
GROUP BY AuditName
does this query always result with the most recent distinct eventname record?
I need to make sure that the recordupdated is the most recent for the eventname
View 2 Replies
View Related
Sep 1, 2007
Hi,
I'm just wondering if someone can help me with some SQL syntax stuff.I want to take this sql statement:
"SELECT TOP 50 tblProfile.chName, tblProfile.intCount FROM tblProfile, tblLinks WHERE (tblLinks.MemberID = tblProfile.MemberID) ORDER BY tblLinks.dtDateAdded DESC;"
and select only unique "chName's" records
View 9 Replies
View Related
Oct 10, 2007
Hello, I have the following tables: declare @B table (Bid int identity, description varchar(50)) declare @P table (Pid int identity, Bid int, description varchar(50)) declare @T table (Tid int identity, description varchar(50)) declare @TinP table (TinPid int identity, Tid int, Pid int) insert into @B (description) select 'B1' insert into @B (description) select 'B2' insert into @P (description, Bid) select 'P1', 1 insert into @P (description, Bid) select 'P2', 1 insert into @P (description, Bid) select 'P3', 2 insert into @T (description) select 'T1' insert into @T (description) select 'T2' insert into @T (description) select 'T3' insert into @TinP (Tid, Pid) select 1, 2 insert into @TinP (Tid, Pid) select 2, 2 insert into @TinP (Tid, Pid) select 3, 3 select * from @B select * from @P select * from @T select * from @TinP I need to get all records in T (Tid and description) which are related to a given BId So for @Bi = 1 I would get: Tid Description 1 T1 2 T2 So I need the distinct values. How to solve this? Thanks, Miguel
View 1 Replies
View Related
Dec 7, 2011
TableName: Order_Archive
Fields:
orderid
load_date
filename
order_date
dollar
I load a file each week into a table, each file has unique orderid, load_date, filename, order_date and dollar. However the same orderid, order_date and dollar could appear in another file with different load_date and file_name.
File1:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-01', 'File1', '2011-01-01', '102'
'1002', '2011-01-01', 'File1', '2011-01-01', '103'
File2:
orderid, load_date, file_name, order_date, dollar
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'
Question:
whats is the best way to retrieve the distinct records that has the most recent load_date? expected results below:
Expected Results:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'
View 3 Replies
View Related
Dec 26, 2013
For examble:
In a table name like
1.Test
2.Test.
3.Test..
4.Test...
How will get the name "Test" using distinct keyword?
View 1 Replies
View Related
May 15, 2008
how can i count in sql the number or records taht would be returned if i did
select distinct site,date from allrecords
View 1 Replies
View Related
Dec 25, 2013
For examble:
In a table name like
1.Test
2.Test.
3.Test..
4.Test...
How will get the name "Test" using distinct keyword?
View 2 Replies
View Related
Jan 11, 2013
I have a table contains information related to sales:
SO number Order Date Customer SellingPerson
1001 2012/07/02 ABC Andy
1002 2012/07/02 XYZ Alan
1003 2012/07/02 EFG Almelia
1004 2012/07/02 ABC John
1005 2012/07/02 XYZ Oliver
1006 2012/07/02 HIJ Dorthy
1007 2012/07/02 KLM Andy
1008 2012/07/02 NOP Rowan
1009 2012/07/02 QRS David
1010 2012/07/02 ABC Joey
Now, i want to write a query using CTE that gives me first five distinct customer in result set:
SO number Order Date Customer SellingPerson
1001 2012/07/02 ABC Andy
1002 2012/07/02 XYZ Alan
1003 2012/07/02 EFG Almelia
1006 2012/07/02 HIJ Dorthy
1007 2012/07/02 KLM Andy
I wrote this query :
With t(so_number,order date,customer, SellingPerson)
as
(select top 5 so_number,order date,customer, SellingPerson from t)
select distinct billingcontactperson from t order by so_id
And getting this error:
Msg 252, Level 16, State 1, Line 1
Recursive common table expression 't' does not contain a top-level UNION ALL operator.
View 9 Replies
View Related
May 21, 2014
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
how can I update Table 1 to get desired results?
View 2 Replies
View Related
Jul 14, 2014
How can i get the total number of each distinct value in the column and write it to another table e.g.
MyTable
Id Fruit
1 Apple
2 Banana
3 Apple
4 Watermelon
5 Banana
6 Watermelon
7 Apple
Result
Fruit Count
Apple 3
Banana 2
Watermelon 2
View 9 Replies
View Related
Jun 20, 2006
1. how to show page number & total page number in report body?
2. how to show total records number?
View 25 Replies
View Related
Jun 1, 2001
Attached is my select query, but it is returning the database values twice.
Can anyone tell me how I tell it to stop! A sub query I guess....
SELECT DISTINCT
dbo.Lead_Entry.Lead_ID, dbo.Lead_Entry.Lead_Source, dbo.Lead_Entry.Lead_Approved, dbo.Lead_Entry.Solution, dbo.Lead_Entry.Lead_Date,
dbo.Customer_Company.Company_Name, dbo.Customer_Contacts.Contact_Name
FROM dbo.Customer_Contacts INNER JOIN
dbo.Customer_Company ON dbo.Customer_Contacts.Company_ID = dbo.Customer_Company.Company_ID CROSS JOIN
dbo.Lead_Entry
View 4 Replies
View Related
Mar 26, 2007
Good day,
I have a table of approximately 10 million rows. The table has 3 field making up the key, namely:
ID, Date, Program
I need to extract all the distinct Program's from the table.
I have don so with:
Select distinct Program from table
This unfortunately takes roughly 2 minutes which is far to long. Is there something I can do to help speed this process up?
Thanks in advance.
View 14 Replies
View Related
Apr 14, 2014
Suppose I have 2 table
1)Main
2)History
Main table maintain all the records having columns MAIN_SKU,DEDUCTIBLE_AMT,model_id,catagory,ModifiedDate
IF DEDUCTIBLE_AMT is changes it will place entry in history table ,columns are same with history_id
i want to display distinct main_sku from history table(all columns) with last DEDUCTIBLE_AMT changed from history table
table structure
main table
MAIN_SKUDEDUCTIBLE_amtmodel_idcatagory
1100100phone
2150101phone
3200109smartphone
4100202smartphone
History table
History_idMAIN_SKUDEDUCTIBLE_amtmodel_idcatagoryModifiedDate
11150100phone4/14/2014
21200101phone4/13/2014
34109202smartphone4/14/2014
44101202smartphone4/13/2014
52200101phone4/13/2014
63100109smartphone4/12/2014
View 3 Replies
View Related
Nov 25, 2014
I have some duplicate records in my table, I need to make those records so they are not duplicate. There is a small dateTime field in that table, I want to add seconds to each row of that field in sequential order so that the record becomes distinct so for e.g if I have this in the field
22-04-2014 10:00:00
22-04-2014 10:00:00
22-04-2014 10:00:00
22-04-2014 10:00:00
So I want to make it like this so that all the duplicate rows becomes distinct
22-04-2014 10:00:01
22-04-2014 10:00:02
22-04-2014 10:00:03
22-04-2014 10:00:04
I am not sure if it is possible, but if it is then how can I achieve this. There are around 20 columns in this table or if there is any other way to make these record distinct.
View 1 Replies
View Related
Oct 17, 2006
Hi
I am trying to write a query that will return a full record with a particular distinct field (the rest of the record being the first such record that includes the distinct field).
For example, for the following:
Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow
Grape Y Green
Grapefruit N Yellow
I would want to return (assuming Colour was the distinct field):
Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow
How do I do this? I've tried using a join (of all different kinds) with a subquery that uses SELECT DISTINCT but this doesn't seem to work. I've tried GROUP BY but none of the aggregate functions seem to just take the first found field.
Thanks for any help you can offer.
View 11 Replies
View Related
Oct 5, 2007
I have the following Table:DataIDuserIDTimeStampI need to create a query that selects the "newest" records for all distinct users. I'm hoping to get this done in one query. I know I could write multiple queries to find all distinct users, and then the newest records based on that... and so on... but I'm not sure that's the most efficient. I have the following so far, but is it the most efficient (I have indexing on most of my columns)? SELECT DATA.DataID, DATA.UserID, UN.vchName, UN.vchImage, U.vchFirstName + ' ' + U.vchLastName AS 'FullName' FROM [tblData] DATA INNER JOIN [tblUnits] UN ON DATA.UserID = Un.UserID INNER JOIN [tblusers] U ON U.UserID = UN.UserID WHERE GPS.intGPSDataID IN (SELECT MAX([tbLData].DataID) FROM [tbLData] GROUP BY [tbLData].UserID)
View 4 Replies
View Related
Nov 3, 2005
I'm exporting the following query to a datagrid, however in the result set, some values are duplicated (for various reasons... mostly old software and poor categorization)...On the records with identical values, I want to look at the account number and the DateOfService fields and search for joint distinct values and only display that...Current Example: ACCT NUM | DATE OF SERVICE |________________________________ 43490 | 10/01/2006 08:15:23 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/24/2005 12:45:30 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/10/2005 12:00:00 | 23489 | 10/15/2006 15:13:23 |Desired Result: ACCT NUM | DATE OF SERVICE |________________________________ 43490 | 10/01/2006 08:15:23 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/24/2005 12:45:30 | 23489 | 10/15/2006 15:13:23 |Here is the query I'm working with... just can't figure out how to join or limit the results to ONLY unique matches in Acct Number AND DateOfService. "SELECT tblCH.ProcedureKey AS CPT, tblPC.Description, DATEDIFF(d, tblPat.BirthDate, " & _ " { fn NOW() }) / 365 AS Age, tblPat.LastName, tblPat.FirstName, tblPat.BirthDate," & _ " CAST(tblCH.AccountKey AS varchar) + '.' + CAST(tblCH.DependentKey AS varchar) AS Account, tblCH.DateOfService " & _ " FROM dbo.Procedure_Code___Servcode_dat tblPC INNER JOIN " & _ " dbo.Charge_History___Prohist_dat tblCH ON tblPC.ProcedureKey = tblCH.ProcedureKey RIGHT OUTER JOIN " & _ " dbo.Patient_Info___Patfile_dat tblPat ON tblCH.AccountKey = (tblPat.AccountKey AND tblCH.DependentKey) = tblPat.DependentKey "Any suggestions from y'all SQL gurus? I have to have this report ready for production by tomorrow morning and this is the last fix I need to make =Thank you =)
View 6 Replies
View Related
Oct 27, 1998
hi, I have a table that contains 11,169,000 rows that was downloaded from the main frame. There are alot of duplicate records in that table. I ran a query select * from tbl.... it still running and running and running ... it never stoped.... what seems to be the problem.... There are no primary keys or index in that table...
so my question , how would I deal with such table ... I want to run certain reports from that table and it seems that all my attempts failed? anyone can help
View 5 Replies
View Related
Oct 24, 2007
I have a table on an offline database having let say 2000 rows and same table on an online database with let say 3000 rows. Now I want to copy just those records (1000) from online database that are not in the offline database table. Also, both tables have same name, same schema etc.
View 3 Replies
View Related
Mar 13, 2008
I have 3 table as below:
DSF
dsf_id
company_name
incorporation_date
secretary_name
ShareholdersDetails
shareholder_id
dsf_id
shareholder_name
DirectorsDetails
directors_id
dsf_id
directors_name
one company can have multiple shareholders and directors records.
i create a search query where users might search by company name, secretary name , shareholder name or directors name.
My select query is like below:
Code:
SELECT dsf.dsf_id, dsf.company_name,
dsf.incorporation_date, dsf.secretary_name,
s.shareholders_name, d.directors_name
FROM tbl_dsf dsf
LEFT OUTER JOIN tbl_directors d on dsf.dsf_id = d.dsf_id
LEFT OUTER JOIN tbl_shareholders s on dsf.dsf_id = s.dsf_id
[WHERE CONDITION]
The result for above query would be like:
Code:
abc | 1/2/1999 | william | marry | donna
abc | 1/2/1999 | william | jenna | donna
abc | 1/2/1999 | william | jolly | donna
abc | 1/2/1999 | william | marry | dolly
abc | 1/2/1999 | william | jenna | dolly
abc | 1/2/1999 | william | jolly | dolly
Is it possible to achive result as below:
Code:
abc | 1/2/1999 | william | marry,jenna,jolly | donna,dolly
Thanks
View 3 Replies
View Related
Jul 7, 2015
I have a task to count distinct records in a big table with roughly 30M records, performance is an important factor. Query is to be written to calculate weekly stats, weekly record number could be as high as 1M.
The actual result is like:
ID Policy
350235744Credit Cards
350235744PCI
350235744PCI Audit
So the final number for this particular Policy is 3
I can write the query like:
select count(distinct Incident_id) policy_name
from Reporting_DailyDlpDetail
Where (year(INSERT_DETECT_TS)=2015) and (month(INSERT_DETECT_TS) =6) and (day(INSERT_DETECT_TS) between 2 and 9)
This returns 526254 and costs 11 seconds to complete
or a query like:
Select distinct Incident_id, policy_name
from Reporting_DailyDlpDetail
Where (year(INSERT_DETECT_TS)=2015) and (month(INSERT_DETECT_TS) =6) and (day(INSERT_DETECT_TS) between 2 and 9)
This returns 749687 and costs roughly 1 minute to complete.
Result is different from the two queries, I believe the later gives correct number. How can I count the distinct based on a combo?Considering the size of data, what is the best and most efficient way to run the stats calculation against over 30 different scenarios (different policies and alert types) and not timeout?
View 9 Replies
View Related
