Count Unique Rows In Each Field
Jul 24, 2013
I have a table in Access 2007 that has about 30 field names and I want to have a count of how many unique rows there are in each field. I want to have these results put into another table that will just have the field name and then the count of how many unique rows there are.
I have code in VBA that will loop through my SQL and change out the field name, but I can't seem to get the SQL right before I can start looping it. For just one field name this would be what I have to count the unique names...
So far I have this:
INSERT INTO newtable
COUNT(*) FROM (SELECT Raw_Table.FieldName, COUNT(Raw_Table.FieldName) AS CountOfFieldName
FROM Raw_Table
GROUP BY Raw_Table.FieldName);
And its not going too well.
View 1 Replies
ADVERTISEMENT
Jul 26, 2015
I have the following query:
Select p.Id [SenderId], p.Username, up.PhotoId,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END AS NewMessage,
p.LastLoggedIn, p.LoggedIn
[Code] ....
The above query returns me all messages (inbox functionality) that have been sent to mr.ReceipientId, the issue I have is when I send another email to the recipient the readdate field will be null, and the other emails linked to the recipient which have also been sent via me will have a readdate date. This causes duplicate rows to appear due to the case statement, I'm trying to figure out if / how it is possible to only display the one row per conversation and set newmessage to 1 if there is an un-read message otherwise show 0 ?
So instead of showing this:
2Person102015-07-26 17:07:24.9370
2Person112015-07-26 17:07:24.9370
I'm trying to get it to look like this:
2Person112015-07-26 17:07:24.9370
Or if we have no new mail (read date is not null) then it will appear like this, and I can confirm when there is no new mail it works as expected.
2Person102015-07-26 17:07:24.9370
View 1 Replies
View Related
May 1, 2015
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.
Result:
PID1
View 2 Replies
View Related
Jun 25, 2007
Hi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
View 6 Replies
View Related
Apr 10, 2008
have a table hits with following fields with sample Data
Code: CreatedOn: HitId
4 2008-04-01 2
4 2008-04-01 2
4 2008-04-02 2
5 2008-04-02 4
5 2008-04-02 4
5 2008-04-02 20
5 2008-04-03 5
4 2008-04-03 6
3 2008-04-04 9
5 2008-04-04 12
____________________________________________________________
i want to create a query to get count of the code per day. And Count Having unique HitId
__________________________________________________________
Code: Date: Total_Count: UniqueHit_Count
3 2008-04-04 1 1
4 2008-04-01 2 1
4 2008-04-02 1 1
4 2008-04-03 1 1
5 2008-04-02 3 2
5 2008-04-03 1 1
------------------------------------------------------------
How can i do this to count code per day and uniqueHit count in a single query?
I am using sql server 2005.
Muhammad Saifullah
View 1 Replies
View Related
Jul 3, 2013
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
View 9 Replies
View Related
Oct 26, 2007
Let's say I have a table MyTable with two colums, One and Two. If I wanted to return unique combinations of these two fields I can do
select distict One, Two from MyTable
How do I return the count of such unique combinations? The following of course does not work but you get the idea.
select count(distinct One, Two) from MyTable
Thanks.
View 6 Replies
View Related
Jul 9, 2015
I've got the following query
SELECT MAX(CountCategories.countOfCategory) AS maxCategory, CountCategories.phoneNumber, CountCategories.category
FROM
(SELECT Categories.phoneNumber, Categories.category, Count(Categories.category) AS CountOfcategory
FROM Categories
GROUP BY Categories.phoneNumber, Categories.category) AS CountCategories
GROUP BY CountCategories.phoneNumber, CountCategories.category;
As you can see I have a sub query that is counting categories for the same phoneNumber and category. I then create a Select query about this as I wanted to only return the highest result. However the above query is returning the same as the subquery.It isn't showing me the unique result for the highest count.
View 1 Replies
View Related
Sep 6, 2013
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.
ID | ID_UNIT
1 | 01
1 | 01
1 | 02
1 | 03
1 | 03
1 | 04
1 | 04
I need two results.
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.
View 3 Replies
View Related
Feb 19, 2014
Aim- Need to count how many [FDMSAccountNo] fall under a unique parentid
This is my query
SELECT [FDMSAccountNo]
,ParentID
FROM [FDMS].[dbo].[Dim_Outlet]
where ParentID = '878028271889'
Which produces the following, The number of fdmsaccountno under parentid is two. Its two because one of the fdmsaccountno is = to parentid
FDMSAccountNo ParentID
878028271889878028271889
878028272887878028271889
878028273885878028271889
Desired result
Parentid #_of_outlets
878028271889 2
View 5 Replies
View Related
Apr 17, 2008
Ok... I know this is something that I've seen and probably done before....
I have a value that will show up multiple times in a table. I want to order the table by that value and then count up from 1 to however many items that it shows up as.
The example below shows the results I want. Value1 and Value2 are in the table, LineNumber is what I need to generate.
Value1
Value2
LineNumber
1
Hey
1
1
Can
2
1
Someone
3
1
Help
4
1
With
5
1
This
6
2
Very
1
2
Simple
2
2
Issue
3
2
As
4
2
I
5
3
Have
1
3
A
2
3
Brain
3
3
Freeze
4
--Thanks--
View 4 Replies
View Related
Sep 2, 2015
updating the # of Payer from below query to match with the # of rows for each payer record. See the Current and desired results below. The query is currently counting the # of rows for all payers together and updating 3 as # of payers. I need it to count # of rows for each payer like shown inDesired result below. It should be showing 1 for first payer and 2 for 2nd & 3rd based on # of times each payer is repeated..
SELECT b.FILING_IND, b.PYR_CD, b. PAYER_ID, b. PAYER_NAME,a.CLAIM_ICN,
(Select Count(*) From MMITCGTD.MMIT_CLAIM a, MMITCGTD.MMIT_TPL b , MMITCGTD.MMIT_ATTACHMENT_LINK c where a.CLAIM_ICN_NU =
c.CLAIM_ICN and b.TPL_TS = c.TPL_TS and a.CLAIM_TYPE_CD = 'X'
[Code] ....
Current Result
FILING_IND
PYR_CD
PAYER_ID
PAYER_NAME
CLAIM_ICN
#_OF_PAYER
[code]....
View 4 Replies
View Related
May 12, 2014
We have a table that has customers invoices and payment records. In some cases a customer has 10 lines with 10 different invoice numbers but may have paid 2 or more invoices with one check. I need to know how many unique payments were made per customer.
Cust# Inv# Chk#
1 109 101
1 110 101
1 111 102
3 112 10003
2 113 799
2 114 800
1 115 103
3 116 10009
2 117 799
1 118 103
So I need the statement to update the customer table with the annual payments
Customer Table
Cust# Payments
1 3
2 2
3 2
I get close but just not getting it to sort itself out.
View 9 Replies
View Related
May 31, 2001
I have a bit field only alow one row as 1 all other rows should always 0.
How can I control that -- trigger or constraint?? How?
View 1 Replies
View Related
Nov 17, 2004
I have a database table with a primary key that auto-increments - using MS SQL Server 2000.
However I have another field (standard field) that must not allow duplicates.
Besides writing code to sift through the database every time to check for duplicates (which would take long to search through the db every time), is there any setting in MS SQL Server 2000 that allows for an automatic check for duplication and will prevent duplicates!??
View 14 Replies
View Related
Nov 18, 2004
I have a database table with a primary key that auto-increments - using MS SQL Server 2000.
However I have another field (standard field) that must not allow duplicates.
Besides writing code to sift through the database every time to check for duplicates (which would take long to search through the db every time), is there any setting in MS SQL Server 2000 that allows for an automatic check for duplication and will prevent duplicates!??
Here's an example of what I mean...
PK Entry_Sheet Cust_Id Date
1 62345 cust1 16/11/2004
2 45663 cust6 16/11/2004
3 82917 cust1 16/11/2004
4 19283 cust2 17/11/2004
5 28764 cust2 18/11/2004
I have everything created, with data already in the table. What I need now is some way to prevent duplicates from occurring.
The Entry_Sheet value needs to be unique. Instead of writing code to run through the entire db each time an insert is performed, is there a simpler way using a SQL Server DB!??
View 12 Replies
View Related
Jun 15, 2004
Is it possible to use the DISTINCT clause on just one field in the SELECT statement?
The following SQL statement causes an error:
SELECT DISTINCT appt.ref, appt.notes FROM Appointments appt
...because DISTINCT can't be used on the notes field as it of type 'text'.
How can I focus the DISTINCT keyword on just the ref field?
(I know ref is the primary key, so this example wouldn't need the DISTINCT keyword, but I've simplified a much more complex statement)
Paul
View 6 Replies
View Related
Nov 30, 2000
I have built a table called Visit_Fact, which is detailed medical data. Within this table I have to create several index fields.
Well one of the fields ([Diag Code]) that should be included will have null values.
Well in Access I was able to build my new field called IndKey which has the following SQL statement:
IndKey: [PracticeCode]&[Physician Code]&[Chart #]&[Visit #]&[InsCoNum]&[CPT]&[Modifier]&[Units]&[Diag Code]
1. Example data with Diag Code = null
ABCD1234ABCDEF1234567MEDI19921301
2. Example data with Diag Code <> null
ABCD1234ABCDEF1234567MEDI19921301708.1
This runs fine in Access and if the Diag Code is null, Access does not care, it still puts all the other information together. Such as the examples above.
Well I tried doing this in a Stored Procedure and when I ran across a record that had a null value for the [Diag Code] it made the IndKey field Null for that record. If it was not null then it looked like Example 2.
What is the proper way of creating my unique field in SQL Server?
View 2 Replies
View Related
Nov 10, 2005
I have a table where the key is an autonumber. I also have a field which holds the reference of a room eg 0BM1. It is nvarchar. Is there a way I can set this field to duplicates = No, so that my user cannot enter the same room reference more than once. Or do I have to do this check in my asp.net code ?
TIA
View 1 Replies
View Related
Jul 10, 2007
Hello,
I would like to store some frequently asked questions in a database.
As those questions/answers could be translated in several languages, I was thinking of using two tables :
The first table would contain a unique field : the key wich is an id for each faq.
The second table would contain those fields :
FAQ_ID : foreign key
LANG : language
QUESTION : the question translated in the good language.
ANSWER : the answer translated in the good language.
What I find embarrassing, is that first table just contains a unique field : the id. But it allows :
- to have a table in which a line correspond to an object. It is convenient for object relational mapping.
- it is more evolutionary. If, in the future, I have to add some statistics that concerns a faq, I will have the possibility to store them in the first table, avoiding in this way to duplicate data.
But, one more time, I find a bit embarrassing having a table with a unique field...
What is your opinion about that ?
Thank you in advance for any suggestion.
mathmax
View 20 Replies
View Related
Jul 23, 2005
Is it possible to have the field as a unique key and a optional one?It is like.. for example, office code has to be unique (cannot beduplicated with the same code) and it could be null too.
View 1 Replies
View Related
Jan 25, 2000
I have a large table that consists of the columns zip, state, city, county. The primary key "zip" has duplicates but the rows are unique.
How do I filter out only the duplicate zips.
Randy Garland
View 2 Replies
View Related
Jun 18, 2004
Hi,
Following is my table:Bets
BetId GameID
500 108
500 109
501 108
501 109
501 110
502 108
502 109
I want BetId 500 and 502 to be returned as result if i give select
criteria where game id = 108,109.
Pls.Note: It should not return BetId 501 in the result, since it belongs to different combination(108,109,110).
Similarly if i give, select criteria where game id =(108,109,110) it should return
BetId 501.not the 500 and 502..which is different combination..
Hope i clarified my problem..pls help me in this regard.Thanks a lot...
View 2 Replies
View Related
Nov 15, 2006
Hi,
I am trying to do a join which involves more than 3 tables. One is a parent table and the other two table have 1:n relationship with that parent table.
But duplicate records are being returned in the resultset. How do I eliminate duplicate records?
This is my query.
SELECT table1.* from table1 left outer join table2 on table1.id=table2.id
left outer join table3 on table1.id=table3.id
I tried doing DISTINCT here but with no success.
SQL with distinct clause.
SELECT distinct table1.id, table1.* from table1 left outer join table2 on table1.id=table2.id
left outer join table3 on table1.id=table3.id
This is the error I get:
The text data type cannot be selected as DISTINCT because it is not comparable
Please help.
View 4 Replies
View Related
Jun 3, 2008
I have the following sql:
SELECT DISTINCT patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE (sub_categoryID = 4 OR patient_record.allocated = 4)
AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5)
GROUP BY patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date
This brings up duplicate records, my aim is to bring distinct records, now if I take out the other returned fields after patientID
and using the following sql:
SELECT DISTINCT patient.patientID
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE (sub_categoryID = 4 OR patient_record.allocated = 4)
AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5)
GROUP BY patient.patientID
This bring up distinct results, but I need to retrieve the other fields from the database i.e. patientFirstName and patientLastName
Please can you help.
View 2 Replies
View Related
Oct 11, 2005
I need to to have a table that has a counter field (used to generate an id code for a record). This is not the primary id for the field but just a counter to label a record in another table, similar to a registration code for an event. This is a sequential counter that will start 1 and go up on each new record inserted into other tables. What I want to do is be able to query this counter table to get the next valid number and increment the count for the next time. This is a multi user web app so needs to prevent duplicate use of same number.How best should this be handled? A transaction to query and update the field?
View 6 Replies
View Related
Oct 18, 2007
SQL 2000 Enterprise Manager won’t let me drop a unique key index and move it to another field. I get an error regarding ‘drop index not allowed because of unique key constraint enforcement.
Is there anyway to change fields for unique keys in SQL Enterprise Manager?
Thanks, Al
View 4 Replies
View Related
Jul 20, 2005
I created a view that joins two tables (tblOrder and tblProduct). The fieldProductID is common to both. When I save the View I get the following errormessage: "Column names in each view must be unique." Is there a way aroundthat? Should I be naming my Key Fields and Foreign Key fields differently?I'm new to MS-SQL. Thanks for your help.Louis
View 2 Replies
View Related
Sep 5, 2006
hello guys,
I have one table which is using to keep 10 difference type of serial number (that mean i got 10 column in the table). Is there any way to do unique key checking (individually, not combine) on these 10 serial number without sacrify the performance?
thank you.
View 1 Replies
View Related
Sep 21, 2006
How would I get the unique email addresses and its associated row of data from a SQL Server table that has no unique fields defined? If there is a duplicate email address then only show the first one and not the other rows with the same email address. Example table and data UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997623 KAIS-22 A.KAIS-22@SampleISD.org997624 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.ZKAIS-22@SampleISD.org997627 KAIS-22 AX.KAIS-22@SampleISD.org Result UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.KAIS-22@SampleISD.org Thanks
View 13 Replies
View Related
Oct 4, 2006
I am a beginner at SQL so thanks ahead of time.....How do I get unique rows based on studentID? Distinct and group by don't seem to workDESIRED RESULTSStudentID First Name Last Name Other Columns...............................................634565491 MARINA BALDERAZ 640484566 TERE BALDERAZCURRENT SQL AND RESULTS.....SELECT ClassRosterRecID, StudentDataRecID, StudentDataKey, StudentID, FirstName, LastName, CurrentGrade, Gender, Ethnicity, EconDisadvantaged, TitleI, Migrant, LEP, Bilingual, ESL, SpecialEducation, GiftedTalented, AtRisk, CareerTech, Dyslexia, LastName + ', ' + FirstName AS LastNameFirstName, EconDisadvantagedSort, TitleISort, MigrantSort, LEPSort, BilingualSort, ESLSort, SpecialEducationSort, GiftedTalentedSort, AtRiskSort, CareerTechSort, DyslexiaSort, DistrictID, CampusID FROM vClassDemographicsDetail WHERE (DistrictID = '057910') AND (CampusID = '057910101') AND (LastName LIKE '%BALDERAZ%')StudentID First Name Last Name Other Columns...............................................634565491 MARINA BALDERAZ 634565491 MARINA BALDERAZ 634565491 MARINA BALDERAZ 640484566 TERE BALDERAZ640484566 TERE BALDERAZ640484566 TERE BALDERAZ
View 1 Replies
View Related
Mar 20, 2008
I got the following query:SELECT TOP (8) ext.extID, ext.Quote, ext.sourceTitle, ext.extRating, gf_game.gameID, gf_game.catID, gf_game.URL, gf_game.TitleFROM gf_game_ext AS ext INNER JOIN gf_game ON gf_game.gameID = ext.gameIDWHERE (ext.Approved = 1)ORDER BY ext.extID DESC which is e.g. producing this output: 6000 -some text- Title 90 1960 2 tom-cl tom cl5999 -some text- title 90 1960 2 tom-clcl asdf5998 -some text- title 90 1959 2 tom-cl-cl asdfWhat I'd like to do now is to filter out the duplicate GameIDs (= 1960) so that just one unique row with the gameid 1960 is remaining. If I put in a SELECT DINSTINCT TOP(8) it just counts for the table ext, but I need it to count for gf_game.gameID - is that possible?Thanks a lot!
View 9 Replies
View Related
May 17, 2007
Using DISTINCT with SELECT have effect only for one column.
But when is needed to select (or to count) queries for all rows for all columns in a table without duplicates, doesn't work.
Select DISTINCT a1,a2,a3,a4 From Y ---> results 167 rows
Select DISTINCT a4 From Y ---> " 85 rows
Any thoughts?
Jorge3921
View 14 Replies
View Related
