CODEWITHSUNDEEP
sqlsql-servert-sql
Mat Richardson
Mat Richardson

Reputation: 3606

SQL Select Puzzle

Ok..here's what I want to do. I've oversimplified the example below:-

I have a table (Table1) with references in like this:

Table1_ID (PK)

Table1_ID Description

There's another table (Table2):-

Table2_ID (PK)

Table2_LinkedID (FK)

Table2_Status <--value is "open" or "complete"

Table2_LinkedID is linked to Table1_ID.

Ok. Now I have three queries that I want to connect together. Here is what I need.

First query:-

SELECT * FROM Table1

This works fine.

I want to add two additional columns to the query. The first is the total number of records in Table2 where the foreign key equals the primary key of table1 (ie SELECT *).

The second will be a count of records where Table2_Status = 'completed'

Does this make sense?

Upvotes: 0

Views: 188

Answers (4)

Lester S
Lester S

Reputation: 730

Will this work for you?

Query 1:

select a.ID
        , count(1) as Table2_RecordCount
from Table1 a
inner join Table2 b on b.LinkedID = a.ID
group by a.ID

Query 2:

select a.ID
        , count(1) as Table2_RecordCount
from Table1 a
inner join Table2 b on b.LinkedID = a.ID
where b.[Status] = 'completed'
group by a.ID

Upvotes: 0

Andr&#225;s Ott&#243;
Andr&#225;s Ott&#243;

Reputation: 7695

You can make a simple GROUP BY with aggregates:

SELECT 
Table1.ID, 
Table1.Description,
Count(Table2.ID) AS TotalT2,
Sum(CASE WHEN Table2.Status = 'completed' THEN 1 ELSE 0 END) AS CountOfCompleted
FROM Table1
LEFT JOIN Table2 ON Table2.LinkedID = Table1.ID
GROUP BY Table1.ID, Table1.Description

Upvotes: 1

Lucero
Lucero

Reputation: 60190

SELECT t1.*,
(SELECT COUNT(*) FROM Table2 WHERE Table2_LinkedID = t1.ID) cntTotal,
(SELECT COUNT(*) FROM Table2 WHERE Table2_LinkedID = t1.ID AND Table2_Status = 'completed') cntCompleted
FROM Table1 t1

Make sure to have a proper index for the foreign key and for Table2_Status for best performance.

Upvotes: 1

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171391

select t1.Table1_ID, 
    t1.Table1_Description, 
    t2.TotalCount, 
    t2.CompletedCount
from Table1 t1
left outer join (
    select Table2_LinkedID, 
        count(*) as TotalCount,
        count(case when Table2_Status = 'completed' then 1 end) as CompletedCount
    from Table2
    group by Table2_LinkedID
) t2 on t1.Table1_ID = t2.Table2_LinkedID

Upvotes: 2

Related Questions