CODEWITHSUNDEEP
sqljoinunion
Bruno Kim
Bruno Kim

Reputation: 2350

joining counts in sql

I'm pretty new to SQL, and am trying to do something not the dumb way. To give some context, I'm comparing the planning and realized uses of vehicles in a carrier branch, and need to count the occurrences of both. I have a with subquery as that returns something like the following

PLANNED  | REALIZED
---------+----------
TRUCK    | BI-TREM
TRUCK    | TRUCK
TRUCK    | TRUCK
TRUCK    | TRUCK
TRUCK    | TRUCK
CARRETA  | CARRETA
CARRETA  | CARRETA
TRUCK    | KOMBI
TRUCK    | BI-TREM
CARRETA  | KOMBI
CARRETA  | KOMBI
TRUCK    | TRUCK
CARRETA  | CARRETA
CARRETA  | BI-TREM
CARRETA  | CARRETA
CARRETA  | CARRETA
TRUCK    | BI-TREM

And would like to return something like the following

VEHICLE | TOTAL_PLANNED | TOTAL_REALIZED
--------+---------------+---------------
CARRETA | 8             | 5
TRUCK   | 9             | 5
BI-TREM | 0             | 4
KOMBI   | 0             | 3

I've tried the following

select PLANNED, 
       count(*) as TOTAL_PLANNED
       null as REALIZED,
       0 as TOTAL_REALIZED
from subquery
group by PLANNED

union all

select null as PLANNED,
       0 as TOTAL_PLANNED,
       REALIZED,
       count(*) as TOTAL_REALIZED
from subquery
group by REALIZED

which returns

CARRETA 8   NULL    0
TRUCK   9   NULL    0
NULL    0   BI-TREM 4
NULL    0   CARRETA 5
NULL    0   TRUCK   5
NULL    0   KOMBI   3

I've also tried all available joins using the subquery, to no success. In fact, RIGHT JOIN works, but only because all planned vehicles are also in the realized side: if some didn't, I would have a NULL at the VEHICLE column.

Thanks for any help, even if just a pointer to a SQL command.

PS.: this query have to work on both SQL Server and Oracle, so I'm striving for pure SQL.

Upvotes: 3

Views: 218

Answers (2)

Simon Wang
Simon Wang

Reputation: 2943

I think this should work:

select T.VEHICLE, 
       (SELECT count(*) FROM Table AS T1 WHERE T1.PLANNED = T.VEHICLE) AS TOTAL_PLANNED
       (SELECT count(*) FROM Table AS T2 WHERE T2.REALIZED = T.VEHICLE) AS TOTAL_REALIZED,
from (SELECT DISTINCT PLANNED AS VEHICLE FROM Table
        UNION SELECT DISTINCT REALIZED AS VEHICLE FROM Table) AS T

Upvotes: 1

user359040
user359040

Reputation:

Try:

SELECT PLANNED_REALIZED AS VEHICLE, 
       SUM(TOTAL_PLANNED) AS TOTAL_PLANNED,
       SUM(TOTAL_REALIZED) AS TOTAL_REALIZED
FROM
(select PLANNED AS PLANNED_REALIZED, 
        1 as TOTAL_PLANNED
        0 as TOTAL_REALIZED
 from subquery
 union all
 select REALIZED as PLANNED_REALIZED,
        0 as TOTAL_PLANNED,
        1 as TOTAL_REALIZED
 from subquery
) SQ
GROUP BY PLANNED_REALIZED

Upvotes: 7

Related Questions