Following will remove errors and will display what you are trying to show-
select * from
(
select count(case License_type_id when 1 then 1 end) as 'FirstNumDrivers',
count(case License_type_id when 2 then 2 end) as 'SecondNumDrivers',
count(case License_type_id when 3 then 3 end) as 'ThirdNumDrivers'
from Drivers
) as s1,
(
select count(case Drivers.License_type_id when 1 then 1 end) as 'OrderMoveFirstNumDrivers',
count(case Drivers.License_type_id when 2 then 2 end) as 'OrderMoveSecondNumDrivers',
count(case Drivers.License_type_id when 3 then 3 end) as 'OrderMoveThirdNumDrivers'
from Order_Move
inner join Drivers on Drivers.Driver_ID = Order_Move.Driver_ID
) as s2
Note that, I just adde
* from
in first line and removed
from Drivers , Order_Move
from the last line to get it work.
However, I know you'll be complaining about multiple no of rows displayed in your result. That's because your query is supposed to do that. It's a
CROSS JOIN
and will show the cartesian product of result from the the subqueries.
For Example:
Check following -
SELECT * FROM
(SELECT 1 AS A1,2 AS A2) AS A,
(SELECT 3 AS B1, 4 AS B2) AS B
Looks you wanted result like this, right?
but it's for just a single record on each of the subqueries. Let's look at a query with more records-
SELECT * FROM
(
SELECT 1 AS A1,2 AS A2
UNION
SELECT 11,22
) AS A,
(
SELECT 3 AS B1, 4 AS B2
UNION
SELECT 33,44
) AS B
Ahh! 4 records, right?
It's 2*2=4 recrds. The cartesian product came in to play.
Now, how to resolve this?
You'll need to apply JOIN based on relation among these tables or subqueries.
I have just removed few things from your query and should look easy and should work.
I haven't executed it in my SSMS so syntax or small errors can be expected.
select count(case License_type_id when 1 then 1 end) as 'FirstNumDrivers',
count(case License_type_id when 2 then 2 end) as 'SecondNumDrivers',
count(case License_type_id when 3 then 3 end) as 'ThirdNumDrivers',
count(case Drivers.License_type_id when 1 then 1 end) as 'OrderMoveFirstNumDrivers',
count(case Drivers.License_type_id when 2 then 2 end) as 'OrderMoveSecondNumDrivers',
count(case Drivers.License_type_id when 3 then 3 end) as 'OrderMoveThirdNumDrivers'
from Drivers
inner join Order_Move on Drivers.Driver_ID = Order_Move.Driver_ID
Please let me know if it doesn't help.
Thanks :)