CODEWITHSUNDEEP
sql
ivias
ivias

Reputation: 249

SQL Query Merge 2 into 1

How could i merge the 2 queries below into 1 with a 4th column to calculate the percentage between column counts 2 and 3

Select DISTINCT ASSN, COUNT(REAPING_GROUP_CODE) as TotalGroups
FROM ReapingGroups
GROUP BY ASSN
Order by ASSN


Select  ASSN, COUNT(REAPING_GROUP_CODE) as Groups
FROM ReapingGroups
WHERE  EXISTS (Select * FROM DeliveryTons where reaping_code = REAPING_GROUP_CODE AND remarks = '')
GROUP BY ASSN

Upvotes: 0

Views: 69

Answers (2)

user681574
user681574

Reputation: 553

Bit of a hack here, but it should get you what you're looking for:

select ASSN, sum(TotalGroups) as TotalGroups, sum(CAST(Groups AS DECIMAL)) / sum(CAST(TotalGroups AS DECIMAL))*100.00 as Percentage
from
(
  (
   Select DISTINCT ASSN, COUNT(REAPING_GROUP_CODE) as TotalGroups, 0 as Groups
   FROM ReapingGroups
   GROUP BY ASSN
  )
  union all    
  (
    Select  ASSN, 0 as TotalGroups, COUNT(REAPING_GROUP_CODE) as Groups
    FROM ReapingGroups
    WHERE  EXISTS (Select * FROM DeliveryTons where reaping_code = REAPING_GROUP_CODE AND remarks = '')
    GROUP by ASSN
  )
) NamedSubQuery
group by ASSN
order by ASSN;

Upvotes: 1

Joe Farrell
Joe Farrell

Reputation: 3542

I won't try to write a specific example, since at the time of this writing you haven't shown any sample data or anything, but here's a general technique you can use to combine a conditional count and an unconditional count into a single query:

select 
    FieldName,
    count(1) -- Unconditional count
    sum(case when /*condition*/ then 1 else 0 end) -- Conditional count
from 
    TableName
group by
    FieldName;

Also, just as an aside: the DISTINCT in your first query is superfluous since you're already grouping on the same field, which is enough to guarantee that each unique ASSN value gets only one record in the result set.

Upvotes: 0

Related Questions