Concatenating values in sql
I am new to SQL and I am trying to do something not stupid. To give some context, I am comparing the planning and realized use of vehicles in the carrier industry and must consider the cases of both. I have with subquery as
one 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 I 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 have 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 have also tried all available joins using subquery with no success. In fact, it RIGHT JOIN
works, but only because all the planned vehicles are also on the conscious side: if someone didn't, I would have NULL in the column VEHICLE
.
Thanks for any help, even if it's just a pointer to a SQL command.
PS: This query should work on both SQL Server and Oracle, so I am aiming for pure SQL.
source to share
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
source to share
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
source to share