How i can i subtract two count selected columns in the same query?

user_14236206 0
Viewed 53

I have a SQL query that am counting the number of items in a table and grouping them by province name. I have another table that am joining that has a column for uploaded that shows only items that have been uploaded. I want to get the pending items from subtracting the two columns.

this is my data

totallabs   LabUploaded
147           0
150           0
156           0
273           0
176           0
244           1
38            1
131           0
197           1
133           1
124           0

This is what am aiming to achieve

totallabs   LabUploaded  Pending
147           0           147
150           0           150
156           0           156
273           0           273
176           0           176
244           1           243
38            1           37
131           0           131
197           1           196
133           1           132
124           0           124

This is my query for the first table

select count(l.facility_name) as totallabs,
count(u.lab_code) as LabUploaded
from labs l 
left join upload u on  u.lab_code = l.mfl_code
inner join province p on p.id = l.province_code
group by p.province_name

I have tried to use SubQueryAlias to give me the data but am not getting the correct data,

select  SubQueryAlias.*
,       totallabs - LabUploaded as pending
from    (
        select  (
                select count(l.facility_name) 
                from   labs l
                ) as totallabs,            
                (
                select  count(u.lab_code) 
                from    upload u
                ) as LabUploaded 
        from    labs l
        left join upload u on  u.lab_code = l.mfl_code 
inner join districts p on p.id = l.district_code
group by p.district_name
        ) as SubQueryAlias

How can I subtract between the two count columns and get the pending data. Any help is appreciated

aggregate-functions sql-server sql
2 Answers

Next time, please provide us some sample data, and the tables, you can use SQL Fiddle to make it easier.

Check if this will suit your needs:

SELECT province_code,
       COUNT(labs.mfl_code) TotalLabs,
       COUNT(uploads.lab_code) Uploads,
       COUNT(labs.mfl_code) - COUNT(uploads.lab_code) Pending
FROM labs
LEFT join uploads ON uploads.lab_code = labs.mfl_code
GROUP BY province_code

SQl Fiddle example: http://sqlfiddle.com/#!9/c689e7/1

If you found my answer useful, i would appreciate if you vote up and mark as accepted.

user_192235860

I'll ignore the commented issues. A simple way to accomplish your goal is to put the working query into a CTE and then calculate the difference by selecting from it. E.g.,

with cte as (
   select count(labs.facility_name) as totallabs,
          count(u.lab_code) as LabUploaded
   from dbo.labs as labs 
   left join dbo.upload as upl on  upl.lab_code = labs.mfl_code
   inner join dbo.province as prov on prov.id = labs.province_code
   group by prov.province_name
)
select cte.*, cte.totallabs - cte.LabUploaded as pending
from cte
order by ...;

Notice the slight additions. Single letter aliases can be confusing. Schema-qualify your table names. Order of rows usually matters and that requires an ORDER BY clause. Use statement terminators. Develop good habits.

user_79261830
Related