SQL Server - Count the number of times the contents of a specified field repeat in a table

debugcn Published at Dev

Timbo

What's the best way to 'SELECT' a 'DISTINCT' list of a field from a table / view (with 'WHERE' criteria) and alongside that count the number of times that that field content repeats in the table / view?

In other words, I have an initial view that looks a bit like this:

Initial Table/View

I'd like a single SQL query to filter it (SELECT...WHERE...) so that we are only considering records where [ORDER COMPLETE] = False and [PERSONAL] = Null...

Filtered out any TRUE values

...and then create a distinct list of names with counts of the number of times each name appears in the previous table:

Distinct list of names with Count field

*Displaying the [ORDER COMPLETE] and [PERSONAL] fields is redundant by this point and could be dropped to simplify.

I can do the steps individually as above, but struggling to get a single query to do it all... any help appreciated!

Thanks in advance,

-Tim

Jamie Pollard

This should just be the following

SELECT dbo.tblPerson.Person,
       COUNT(dbo.tblPerson.Person) AS Count
  FROM dbo.tblPerson
INNER JOIN dbo.tblNotifications ON dbo.tblPerson.PersonID = dbo.tblNotifications.AddresseeID
 WHERE dbo.tblNotifications.Complete = 'False'
   AND dbo.tblNotifications.Personal IS NULL
GROUP BY dbo.tblPerson.Person
ORDER BY COUNT(dbo.tblPerson.Person) DESC

You don't need your DISTINCT or TOP 100 PERCENT,

Here is a simplified fiddle

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-7

Comments

0 comments

From Java

Related Related

Article

SQL Server - Count the number of times the contents of a specified field repeat in a table

SQL Server - Count the number of times the contents of a specified field repeat in a table

Repeat array rows specified number of times

Repeat each item in a list a number of times specified in another list

SQL Server column was specified multiple times

Count number of times 2 distinct values appear in more than 1 row in SQL Table

Count number of times string matches a field across multiple documents

Count in s Table SQL Server

SQL query to get number of times a field repeats for another specific field

Count the number of times each members name appears in a table with a condition

Count the number of times each members name appears in a table with a condition

sql - show count of field from another table

sql server count distinct value of a field

SQL query to count the number of times a term appears if the term appears

Count the Number of Tables in a SQL Server Database

How to count number of people in submission SQL Server

FLWOR in Sql server count number of hits

count the number of spaces in values in sql server

SQL Server: Count number across columns

FLWOR in Sql server count number of hits

How to count number of people in submission SQL Server

Repeat a Number N Times in an Array

Get the number of same field from a table in SQL

How can I repeat each text field in a column the number of times given in another column?

count number of values in each field in awk, output table

Count of Columns in temp table in SQL Server

How to get the count of a join table in sql server?

sql server, aggregate (count) on many columns in table

SQL Server - Insert record count into table

Make a copy of a table in the same database of SQL Server 2008 and then update the contents

How to Count Number of in in a field