CODEWITHSUNDEEP
sqlsql-server-2008
Rob Peskind
Rob Peskind

Reputation: 1

Add column to SQL result based on result of first query

I'm struggling with what should be an easy query, I think.

I have two tables, units and events. My first query is:

select u.unit, u.group, e.zone  
from units u 
left join event e
on u.id = e.id
where u.ag_id = 'FIRE' and u.numbr = 'G0182' and u.status > 1

where the result is:

Unit | Group | Zone
M1      44G     8
M2      50F     7

I then run another query to determine how many total units from each group are active:

select count(1) from units where group = '[result]' and status > 1

where [result] is the group output from the first query (44G, 50F, etc)

I'd like to add a 4th column to my first query that just adds the total on that row, like this:

Unit | Group | Zone | Active
M1      44G     8       4
M2      50F     7       3

How can I chain these queries to complete it all in one statement?

Thanks!

Upvotes: 0

Views: 207

Answers (3)

Irakli dd
Irakli dd

Reputation: 56

SELECT *
FROM
  (SELECT u.unit,
          u.group,
          e.zone,
          count(u.group) over(partition BY u.group) active
   FROM units u
   LEFT JOIN event e ON u.id = e.id
   WHERE u.status > 1) uu
WHERE uu.ag_id = 'FIRE'
  AND uu.numbr = 'G0182'

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74625

When you want to make an SQL resultset wider, you use a JOIN

select u.unit, u.group, e.zone, x.ctr
from units u 
left join event e on u.id = e.id

INNER JOIN(select group , count(*) as ctr from units where status > 1 GROUP BY group) X ON X.group = u.group


where u.ag_id = 'FIRE' and u.numbr = 'G0182' and u.status > 1

It seemed to me that the first query would exclude items (thanks to its where clause) that you later wanted to count. This means you go back to the source data with your new requirements (where status > 1) and group and count them into a separate result, that you then put as a subquery and join in.

Remember, tables can appear multiple times in a query; a table is just a block of data, as is the output from a select. SQL server joins blocks of data to other blocks of data, it doesn't care whether it's a table or formed from the output of a query.

To make data block wider - JOIN, taller - UNION, shorter - WHERE, and narrower - SELECT. Mostly, SQL is about chopping up blocks of data and sticking them back together to make different shaped rectangles :D

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You can use window functions:

select u.unit, u.group, e.zone, u.cnt  
from (select u.*,
             sum(case when status > 1 then 1 else 0 end) over (partition by u.group) as cnt
      from units u 
     ) u left join
     event e
     on u.id = e.id
where u.ag_id = 'FIRE' and u.numbr = 'G0182' and u.status > 1

Upvotes: 0

Related Questions