Clever Geek Handbook

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.

+3


source to share


2 answers


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
+7


source


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
+1


source






More articles:


All Articles