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

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

CodePudding user response：

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.

CodePudding user response：

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.