CODEWITHSUNDEEP
sqlsql-server
moonninja
moonninja

Reputation: 53

How to create a SQL query result from multiple select statements(WITHOUT UNION)

I have a few select statements that when executed alone return what I need. I want to combine all these select statements into what looks like a normal table. I tried UNION but get an error that says I need the same number of arguments( column fields obviously),that however is not what I want. So I tried this(my code so far)

SELECT Company_Code FROM Company
UNION
SELECT Founder FROM Company
UNION
SELECT CAST(COUNT(Company.id) as varchar(5)) as Number_of_LeadManagers
FROM Lead_Manager
LEFT JOIN Company
ON (Lead_Manager.Companyid = Company.id)
GROUP BY Company.id
UNION
SELECT COUNT(Company.id) as Number_of_SeniorManagers
FROM Senior_Manager
LEFT JOIN Company
ON (Senior_Manager.Companyid = Company.id)
GROUP BY Company.id
UNION
SELECT COUNT(Company.id) as Number_of_Managers
FROM Manager
LEFT JOIN Company
ON (Manager.Companyid = Company.id)
GROUP BY Company.id
UNION
SELECT COUNT(Company.id) as Number_of_Employees
FROM Employee
LEFT JOIN Company
ON (Employee.Companyid = Company.id)
GROUP BY Company.id

This entire statement return everything I want below each other. Taking my code if each select statement is run alone it return exactly what I want(always return two rows) What I want is for the final result to Just put all these select statements results next to each other so this will show 2 rows with however many columns was returned by the select statements.

Idea of what I want for clarification If I execute 

SELECT Company_Code,Founder FROM Company

I get

**Result1**
row 1 :  C1 Monika
row 2 :  C2 Samantha

(ONLY 2 ROWS ALWAYS) If I execute 

   SELECT CAST(COUNT(Company.id) as varchar(5)) as Number_of_LeadManagers
    FROM Lead_Manager
    LEFT JOIN Company
    ON (Lead_Manager.Companyid = Company.id)
    GROUP BY Company.id

I get

**Result2**
row 1 : 1
row 2 : 1

The other select statements are basically the same just using different tables to count from. All I want is a query that will allow me to put Result1 and Result2 next to each other(as well as the rest of the select results) Desired final result

**Result3**
row 1 : C1 Monika 1
row 2 : C2 Samantha 1

PS.Started SQL last week still struggling , this is a homework project by the way. If my way of thinking is wrong let me know but that is how I see it in my brain and cannot quite figure out how to SQL it ;P

Upvotes: 2

Views: 6198

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

If you have a string column then you need s string column for each select in union c

So you just need cast each count as char as in the first select 

SELECT Company_Code FROM Company
UNION
SELECT Founder FROM Company
UNION
SELECT CAST(COUNT(Company.id) as varchar(5)) as Number_of_LeadManagers
FROM Lead_Manager
LEFT JOIN Company
ON (Lead_Manager.Companyid = Company.id)
GROUP BY Company.id
UNION
SELECT CAST(COUNT(Company.id) as varchar(5)) as Number_of_SeniorManagers
FROM Senior_Manager
LEFT JOIN Company
ON (Senior_Manager.Companyid = Company.id)
GROUP BY Company.id
UNION
SELECT CAST(COUNT(Company.id)  as varchar(5)) as Number_of_Managers
FROM Manager
LEFT JOIN Company
ON (Manager.Companyid = Company.id)
GROUP BY Company.id
UNION
SELECT CAST(COUNT(Company.id) as varchar(5)) as Number_of_Employees
FROM Employee
LEFT JOIN Company
ON (Employee.Companyid = Company.id)
GROUP BY Company.id

but if you need all the results on the same rows you could use left join on the subquery 

SELECT Company_Code , Founder
  , t1.Number_of_LeadManagers
  , t2.Number_of_SeniorManagers
  , t3.Number_of_Managers 
  , t4.Number_of_Employees
FROM Company
LEFT JOIN (
SELECT Company.id, COUNT(Company.id) as Number_of_LeadManagers
FROM Lead_Manager
LEFT JOIN Company ON Lead_Manager.Companyid = Company.id
GROUP BY Company.id
) t1 on t1.id = company.id
LEFT JOIN (
SELECT  company.id, COUNT(Company.id)  as Number_of_SeniorManagers
FROM Senior_Manager
LEFT JOIN Company
ON (Senior_Manager.Companyid = Company.id)
GROUP BY Company.id
) t2 ON t2.id = company.id
LEFT JOIN (
SELECT company.id, COUNT(Company.id) as Number_of_Managers
FROM Manager
LEFT JOIN Company
ON (Manager.Companyid = Company.id)
GROUP BY Company.id
) t3 ON t3.id = company.id
LEFT JOIN (
SELECT company.id, COUNT(Company.id) as Number_of_Employees
FROM Employee
LEFT JOIN Company
ON (Employee.Companyid = Company.id)
GROUP BY Company.id
) t4 ON t4.id = company.id

Upvotes: 1

Shawn
Shawn

Reputation: 4786

The simplest way to get all of them on the same row would just be with correlated subqueries.

SELECT Company.Company_Code, Company.Founder 
  , (
        SELECT COUNT(Company.id) 
        FROM Lead_Manager
        WHERE Lead_Manager.Companyid = Company.id
    ) AS Number_of_LeadManagers
  , (
        SELECT COUNT(Company.id) 
        FROM Senior_Manager
        WHERE Senior_Manager.Companyid = Company.id
    ) AS Number_of_SeniorManagers
  , (
        SELECT COUNT(Company.id) 
        FROM Manager
        WHERE Manager.Companyid = Company.id
    ) AS Number_of_Managers
  , (
        SELECT COUNT(Company.id) 
        FROM Employee
        WHERE Employee.Companyid = Company.id
    ) AS Number_of_Employees
FROM Company

Depending on your data, you may need to further eliminate duplicates.

Upvotes: 0

Related Questions