asked on 7/2/2021

Sum of field case for distinct records

I have a MS SQL table where one field is common for some of the records (link) and I need to calculate the count of some other fields in that same table based upon the case value of those fields but for only the distinct records. For example...
id = record id
link =common id
type = case

id = 1, link = 1, type = "out"
id = 2, link = 1, type = "out"
id = 3, link = 1, type = "out"
id = 4, link = 2, type = "out"
id = 5, link = 2, type = "out"
id = 6, link = 3, type = "in"
id = 7, link = 3, type = "in"
id = 8, link = 3, type = "in"
id = 9, link = 4, type = "out"
id = 10, link = 4, type = "out"

Results that I need
link count = 4
case "out" count = 3
case "in" count = 1

Can this be done using a single query statement?

Microsoft SQL Server

Last Comment

Greg Watkins

7/2/2021

Brian Crowe

7/2/2021

DECLARE @Table TABLE
(
   id      INT         IDENTITY(1,1) NOT NULL,
   link   INT         NOT NULL,
   type   VARCHAR(10)   NOT NULL
);

INSERT @Table
(
    link,
    type
)
VALUES
   (1, 'out'),
   (1, 'out'),
   (1, 'out'),
   (2, 'out'),
   (2, 'out'),
   (3, 'in'),
   (3, 'in'),
   (3, 'in'),
   (4, 'out'),
   (4, 'out');

WITH cteDistinctLink AS
(
   SELECT DISTINCT link, type
   FROM @Table
)
SELECT COUNT(*) AS LinkCount,
   SUM(CASE WHEN type = 'out' THEN 1 ELSE 0 END) AS OutCount,
   SUM(CASE WHEN type = 'in' THEN 1 ELSE 0 END) AS InCount
FROM cteDistinctLink

Open in new window

Greg Watkins

7/2/2021

ASKER

Thanks for the quick reply Brian. In your example I understand that you are building the table based upon my example and then using that table but my table already exists. Let's say my table name = "Calls". This might be a dumb question but, how do I do the same query for an existing table?

ASKER CERTIFIED SOLUTION

Brian Crowe

7/2/2021

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Greg Watkins

7/2/2021

ASKER

DUH, I got it now! Thanks!