Oracle SQL combine 2 queries from same table with output in 2 different columns

Question

I'm using an Oracle SQL database. I'm trying to combine this 2 queries into one from same table:

select count(CustomerID) from Customers
where (City = 'Berlin' or City = 'London')
from Customers;

select count(CustomerID) from Customers
where (City = 'Mannheim' or City = 'Strasbourg')
from Customers;

And my output would like to be something like exported on 2 columns:

| Berlin & London | Mannheim & Strasbourg |
|-----------------------------------------|
|        5        |         10            |

example of db here, https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

I think the answer will be:

(select count(CustomerID) from Customers
where (City = 'Berlin' or City = 'London'))
union
(select count(CustomerID) from Customers
where (City = 'Marseille' or City = 'Tsawassen'));
union
(select count(CustomerID) from Customers
where (City = 'Strasbourg' or City = 'Madrid'));

Still testing...

Finally got the job done by using this case. Thanks to everyone involved !

SELECT
  COUNT(CASE
    WHEN  work_zone = 'AMBGI01' OR
          work_zone = 'AMBGI02' OR
          work_zone = 'AMBGI03' AND
          task_type = 'P' THEN work_zone
  END) "HighBay Putaways",
  COUNT(CASE
    WHEN  work_zone = 'AMBGI04' OR
          work_zone = 'AMBGI05' OR
          work_zone = 'AMBGI06' OR
          work_zone = 'AMBGI07' AND
          task_type = 'P' THEN work_zone
  END) "LowBay Putaways",
  COUNT(CASE
    WHEN  from_loc_id = 'THEWALL' AND
          task_type = 'P' THEN from_loc_id
    END) "THE WALL",
    COUNT(CASE
    WHEN  tag_id like '%' AND
          from_loc_id like 'EXPANS%' AND
           task_type = 'P' THEN tag_id
     END) "EXPANSION",
   COUNT(CASE
    WHEN  final_loc_id like '_______' AND
          (status = 'In Progress'        OR
          status = 'Released')          AND
          task_type = 'R' THEN final_loc_id
  END) "Replens"
FROM move_task;

is there any relational key between country and customer table? — Fahmi
– Fahmi, Commented Oct 18, 2018 at 8:20
Please edit the question to show the structure of the Country and Customers tables. — BriteSponge
– BriteSponge, Commented Oct 18, 2018 at 9:45
i think the asnwer will be (select count(CustomerID) from Customers where (City = 'Berlin' or City = 'London')) union (select count(CustomerID) from Customers where (City = 'Marseille' or City = 'Tsawassen')); union (select count(CustomerID) from Customers where (City = 'Strasbourg' or City = 'Madrid')); but not sure if working 100% — body
– body, Commented Oct 18, 2018 at 9:53

Radagast81 · Accepted Answer · 2018-10-18 09:57:28Z

2

Easiest way is the following:

select count(CASE WHEN City = 'Berlin' or City = 'London' THEN City END) "Berlin & London"
     , count(CASE WHEN City = 'Mannheim' or City = 'Strasbourg' THEN City END) "Mannheim & Strasbourg"
  from Customers

answered Oct 18, 2018 at 9:57

Radagast81

3,0461 gold badge9 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

body Over a year ago

trying it on the link I provided, I got an error .. try it as well, maybe I am doing something wrong.. ?

Radagast81 Over a year ago

The database in your link doesn't support Oracle syntax. Which database type do you use?

body Over a year ago

Oracle at work, should try this at work and then come with a feedback ;) will get back asap

Ponder Stibbons · Accepted Answer · 2018-10-18 09:59:10Z

2

Its not clear if you have two tables (customers, country) or one. If there are two you need to show us structure. w3schools.com says there is no such table like country and customers does not contain store column. Anyway you need conditional count, something like here:

select count(case when city in ('Berlin', 'London')       
                  then 1 end) as "Berlin & London",
       count(case when city in ('Mannheim', 'Strasbourg') 
                  then 1 end) as "Mannheim & Strasbourg"
  from Customers
  where city in ('Berlin', 'London', 'Mannheim', 'Strasbourg')

You could also use pivot, but this is simpler, more readable and works in older Oracle versions.

answered Oct 18, 2018 at 9:59

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

1 Comment

body Over a year ago

Customers table only sorry

Collectives™ on Stack Overflow

Oracle SQL combine 2 queries from same table with output in 2 different columns

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related