Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Question

0.00/5 (No votes)

See more:

i want select count from 2 table in one table row
i write this code ,

SQL

select 
(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 from Drivers , Order_Move

What I have tried:

i want make this query in one row not one columns

Posted 31-Jul-16 23:46pm

MahmoudOmar

Updated 1-Aug-16 1:10am

King Fisher

v2

Add a Solution

Comments

King Fisher 1-Aug-16 6:09am

try like this,
select *From (SELECT City FROM Customers) as a, (SELECT City FROM Customers)as b

MahmoudOmar 1-Aug-16 6:50am

thanks it's working

King Fisher 1-Aug-16 6:55am

Welcome :)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Suvendu Shekhar Giri · Accepted Answer · 2016-08-01T01:10:00

Following will remove errors and will display what you are trying to show-

SQL

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 -

SQL

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-

SQL

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.

SQL

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 :)