Sql Query to Get Number of Times a Field Repeats for Another Specific Field

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

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

count repeated records in a table of a specific column

you were close you just had the order wrong:

Select [NewsItemTitle], count(1) as xx from [MobileServiceExtra].[ACTIVITY]
WHERE [Location] = 'NewYork'
Group by [NewsItemTitle]
Order BY xx Desc

Selecting the number of times an ID field appears in another table

If you only want customers that have placed an order, then use EXISTS:

SELECT TOP (100)
       c.CustomerID,
       c.FirstName,
       c.LastName,
       c.Email,
       c.Phone,
       a.Company,
       a.Address1,
       a.Address2,
       a.Suite,
       a.City,
       a.State,
       a.Zip,
       a.Country
FROM Customer c
     JOIN Address a ON c.CustomerID = a.CustomerID
WHERE EXISTS (SELECT 1
              FROM Orders O
              WHERE O.CustomerID = C.CustomerID)
ORDER BY (SELECT NULL); --Whoop whoop, 100 random rows

I did comment this under the question, but to put in the answer, I have added an ORDER BY. If you provide TOP without an ORDER BY then you are effectively saying to the Server "Return 100 Random rows". I have used (SELECT NULL), as I don't know what order you want your rows, so you'll need to change that.

How to get the count of duplicate value for all columns of a table

select key, count(distinct value)
from (select (jsonb_each(to_jsonb(t.*))).* from pg_class as t) as tt
group by key;

It is definitely not the most efficient solution but it is applicable for any table. Just replace pg_class by the desired table name.

PS: I got a lot of pain proposing this solution. Imagine the table with 100M rows and 100 columns. Then PostgreSQL should to build and sort the intermediate data with 10000000000 rows.

If you don't want the exact numbers but only evaluative then look at the pg_stats table at the n_distinct column in particular.

SQL - Find duplicate fields and count how many fields are matched

You are just missing the magic of CUBE(), which generates all the combinations of columns automatically

DECLARE @duplicate_column_threshold int = 5;

WITH cte AS (
  SELECT
    field1,field2,...,field10
   ,duplicate_column_count = (SELECT COUNT(col) FROM (VALUES (field1),(field2),...,(field10)) c(col))
  FROM table_name
  GROUP BY CUBE(field1,field2,...,field10)
  HAVING COUNT(*) > 1
)
SELECT *
INTO #duplicated_rows
FROM cte
WHERE duplicate_column_count >= @duplicate_column_threshold

Update: to fetch the rows from the original table, join it against the #duplicated_rows using a technique that treats NULLs as wildcards when comparing the columns.

SELECT
  a.* 
 ,b.duplicate_column_count
FROM table_name a 
INNER JOIN #duplicated_rows b
  ON  NULLIF(b.field1,a.field1) IS NULL
  AND NULLIF(b.field2,a.field2) IS NULL
  ...
  AND NULLIF(b.field10,a.field10) IS NULL

Count the number of times a data is repeated in the table sql Query

So the basic idea is the following:

SELECT (A BUNCH OF FIELDS)
FROM (GIVE ME THE JOINED TABLES (PERSON + INTERMEDIARY + HOME))
WHERE
    Person.id = (
        (GIVE ME THE ID OF THE PERSON WHOS ID IS APPEARING EXACTLY 2x INSIDE INTERMEDIARY)
    )

Joining the tables is easy enough with two consecutive JOINs:

(Person JOIN Intermediary ON Person.id = person) JOIN Home ON Intermediary.home = Home.id

For the id of the person who is appearing twice in the intermediary table, we just count how often each person appears in the table by using the GROUP BY operator:

SELECT person, COUNT(person) as 'count' FROM Intermediary GROUP BY person

...from which we select only the rows where count = 2, by wrapping the just mentioned query (Q) like so:

SELECT person FROM ( Q ) AS personHomeCount WHERE personHomeCount.count = 2 LIMIT 1

It is assumed that a combination of a person and a home is unique and can only appear once in the table. Additionally, note the LIMIT 1 as we can only accept one value returned by the query. If multiple people have exactly two homes, the first person will be the selected one.

Finally, stiching these queries together, we get:

SELECT
    Person.name,
    Person.id as 'id of person',
    Home.location as 'location of home'
FROM
    (
        (
            Person
            JOIN Intermediary ON Person.id = person
        )
        JOIN Home ON Intermediary.home = Home.id
    )
WHERE
    Person.id = (
        SELECT
            person
        FROM
            (
                SELECT
                    person,
                    COUNT(person) as 'count'
                FROM
                    Intermediary
                GROUP BY
                    person
            ) AS personHomeCount
        WHERE
            personHomeCount.count = 2 LIMIT 1
    )

Here is the SQL-Fiddle for further demonstration: https://www.db-fiddle.com/f/nJrmwtQfASzTPp851Q9SSf/0

Hope I could help!

Count the occurrences of duplicate values among columns of a table

try this:

SELECT Col, COUNT(*) AS TOT
FROM (
    SELECT ColumnA AS Col FROM table
    UNION ALL
    SELECT ColumnB FROM table
    UNION ALL
    SELECT ColumnC FROM table
) AS A
GROUP BY Col

Finding duplicate values in a SQL table

SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

Simply group on both of the columns.

Note: the older ANSI standard is to have all non-aggregated columns in the GROUP BY but this has changed with the idea of "functional dependency":

In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.

Support is not consistent:

Recent PostgreSQL supports it.
SQL Server (as at SQL Server 2017) still requires all non-aggregated columns in the GROUP BY.
MySQL is unpredictable and you need sql_mode=only_full_group_by:
- GROUP BY lname ORDER BY showing wrong results;
- Which is the least expensive aggregate function in the absence of ANY() (see comments in accepted answer).
Oracle isn't mainstream enough (warning: humour, I don't know about Oracle).

SQL Query To Obtain Value that Occurs more than once

For SQL Server 2005+

;WITH T AS
(
SELECT *, 
       COUNT(*) OVER (PARTITION BY Lastname) as Cnt
FROM Students
)
SELECT * /*TODO: Add column list. Don't use "*"                   */
FROM T
WHERE Cnt >= 3