CODEWITHSUNDEEP
sqlsql-serverjoinleft-join
slayernoah
slayernoah

Reputation: 4492

SQL Server: Select multiple fields and display the count of duplicates in one of the columns

I have a SQL Server table with the following fields and sample data:

USERS TABLE

ID         Name   Address
23052-PF   Peter  Timbuktu
23052-D1   Jane   Paris
23052-D2   David  London
23050-PF   Sam    Beijing
23051-PF   Nancy  NYC
23051-D1   Carson Cali
23056-PF   Grace  LA

AGE TABLE

ID         Age
23052-PF   25
23052-D1   22
23052-D2   25
23050-PF   22
23051-PF   26
23051-D1   22
23056-PF   28

I need to return ID, Name, Address, Age and number of occurrences of the Age column in a separate column.

The table needs to be sorted in descending order based on the count of the age column.

The output for the sample data above needs to be as below:

ID         Name   Address   Age  Age(Count)
23052-PF   Peter  Timbuktu  25   3
23056-PF   Grace  LA        25   3
23051-D1   Carson Cali      25   3
23052-D1   Jane   Paris     22   2
23050-PF   Sam    Beijing   22   2
23052-D2   David  London    24   1
23051-PF   Nancy  NYC       26   1

Thanks for all your help!

Upvotes: 0

Views: 714

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Here is another approach:

select u.id, u.name, u.address, a.age, count(*) over (partition by ages) as "Age(Count)"
from users u join
     ages a
     on u.id = a.id
order by "Age(Count)" desc

You can also use:

order by 4 desc

although use of column numbers in order by is discouraged.

Upvotes: 3

JoeFletch
JoeFletch

Reputation: 3960

This should get you what you want.

CREATE TABLE #u
(
    id          nvarchar(100),
    Name        nvarchar(100),
    Address     nvarchar(100)
)

INSERT INTO #u
SELECT N'23052-PF', N'Peter',  N'Timbuktu' UNION
SELECT N'23052-D1', N'Jane',   N'Paris'    UNION
SELECT N'23052-D2', N'David',  N'London'   UNION
SELECT N'23050-PF', N'Sam',    N' Beijing' UNION
SELECT N'23051-PF', N'Nancy',  N'NYC'      UNION
SELECT N'23051-D1', N'Carson', N'Cali'     UNION
SELECT N'23056-PF', N'Grace',  N'LA'

CREATE TABLE #a
(
    id          nvarchar(100),
    Age         int
)

INSERT INTO #a
SELECT N'23052-PF', 25 UNION
SELECT N'23052-D1', 22 UNION
SELECT N'23052-D2', 25 UNION
SELECT N'23050-PF', 22 UNION
SELECT N'23051-PF', 26 UNION
SELECT N'23051-D1', 22 UNION
SELECT N'23056-PF', 28

;WITH
cte AS
(
    SELECT
        u.id,
        u.Name,
        u.Address,
        a.Age,
        COUNT(*) OVER (PARTITION BY a.Age) AS [Age(Count)]
    FROM
        #u AS u INNER JOIN #a AS a
            ON
                u.id = a.id
)
SELECT
    *
FROM
    cte
ORDER BY
    [Age(Count)] DESC

Upvotes: 3

Related Questions