How to add two SELECT statments together-CodePudding

I have two separate queries which collect relevant data and count the number of two different services (sms&phone plan, data plan) that telecommunication service offers, both queries are able to get the data from the specified fields, but I wish to do everything using one queries.

SELECT a.OperatorName, count(b.planName) as 'Plans'
FROM dbo.Operator a INNER JOIN dbo.Plan b
on a.OperatorID = b.OperatorID
GROUP BY a.OperatorName

SELECT a.OperatorName, count(B.intplanName) as 'Plans'
FROM dbo.Operator AS a
    LEFT JOIN
    InternetPlan as B
    ON a.OperatorID = B.OperatorID
    GROUP BY a.OperatorName

Output[First]:

OperatorName           | Plans
Verizon                   6
Google                    10
SomethineElse             4

Output[Second]:

OperatorName           | Plans
Verizon                   2
Google                    9
SomethineElse             9

Desired output

OperatorName           | Plans
Verizon                   9
Google                    19
SomethineElse             13

Both of these queries return the relevant information, the issue starts when I try to combine them.

The below-specified code does not work, I'm unsure as to where it doesn't work properly and why.

SELECT a.OperatorName, count(b.planName) as 'Plan'
FROM dbo.Operator AS a
INNER JOIN 
Plan as b
on a.operatorID = b.operatorID
INNER JOIN
InternetPlan as c
on c.operatorID = b.operatorID
GROUP BY a.OperatorName

The above query outputs the following:

OperatorName           | Plans
Verizon                   12
Google                    90
SomethineElse             36

And it should be

OperatorName           | Plans
Verizon                   9
Google                    19
SomethineElse             13

CodePudding user response：

You should remove the counting from the inner query like so:

SELECT tem.Plan, count(Plan) FROM
(
    SELECT a.OperatorName, b.planName as 'Plan'
    FROM dbo.Operator a INNER JOIN dbo.Plan b
    on a.OperatorID = b.OperatorID
    UNION ALL
    SELECT a.OperatorName, b.intplanName as 'Plan'
    FROM dbo.Operator a INNER JOIN dbo.InternetoPlan b
    on a.OperatorID = b.OperatorID
) AS tem
GROUP BY Plan
ORDER BY Plan

CodePudding user response：

Building on @sagi's answer, you could pull Operator to the outside of the union, to avoid querying it twice

SELECT
  o.OperatorName,
  COUNT(*)
FROM dbo.Operator o
INNER JOIN 
(
    SELECT p.OperatorID, p.planName as Plan
    FROM dbo.Plan p
    UNION ALL
    SELECT ip.OperatorID, ip.intplanName as Plan
    FROM dbo.InternetoPlan ip
) AS p ON p.OperatorID = o.OperatorID
GROUP BY o.OperatorID, o.OperatorName
ORDER BY o.OperatorName;

CodePudding user response：

Per operator you want to get the plan count and the Internet plan count. So, select from the operator table and join the two counts:

select o.operatorname, coalesce(p.cnt, 0)   coalesce(ip.cnt, 0) as plans
from dbo.operator o
left join
(
  select operatorid, count(*) as cnt
  from dbo.plan
  group by operatorid
) p on p.operatorid = o.operatorid
left join
(
  select operatorid, count(*) as cnt
  from internetplan
  group by operatorid
) ip on ip.operatorid = o.operatorid
order by o.operatorname;

The same can be achieved with subqueries in the SELECT clause or with lateral joins (OUTER APPLY).