Need one resultset from different sql quries in the stored procedure

[mmazeemahmad]

Hi i am making a stored procedure with multiple select statements when i execute them than different resultset are maken for each select statement all i need is to get one resultset from all select statements of sql quries in the stored procedure......HERE is the SP code..

SET NOCOUNT ON;

      Select COUNT(*) as totalEmployees From dbo.Add_Employees
  
	  Select DISTINCT dbo.Add_Employees.gender, COUNT(Add_Employees.employee_uniqueId) as AccGender
	  From dbo.Add_Employees
	  Group BY(Add_Employees.gender)
	  
	  Select DISTINCT dbo.Add_Employees.department, COUNT(Add_Employees.employee_uniqueId) as Accdepartment
	  From dbo.Add_Employees
	  Group BY(Add_Employees.department)

	  
	  Select DISTINCT dbo.Add_Employees.designation, COUNT(Add_Employees.employee_uniqueId) as Accdesignation
	  From dbo.Add_Employees
	  Group BY(Add_Employees.designation)

	  Select DISTINCT dbo.Add_Employees.grade, COUNT(Add_Employees.employee_uniqueId) as AccGrade
	  From dbo.Add_Employees
	  Group BY(Add_Employees.grade)
	  
	  Select DISTINCT dbo.Add_Employees.country, COUNT(Add_Employees.employee_uniqueId) as AccCountry
	  From dbo.Add_Employees
	  Group BY(Add_Employees.country)
	  
	  Select DISTINCT dbo.Add_Employees.city, COUNT(Add_Employees.employee_uniqueId) as AccCity
	  From dbo.Add_Employees
	  Group BY(Add_Employees.city)
	  
	  
END

[urtrivedi]

You can use union statment to merge multiple query result in one.
But take care all queries must have same number of columns and of same type from proper output

SELECT DISTINCT  'country' coltype, dbo.Add_Employees.country colvalue, COUNT(Add_Employees.employee_uniqueId) AS total FROM dbo.Add_Employees
	  GROUP BY(Add_Employees.country)

UNION

SELECT DISTINCT  'city' coltype, dbo.Add_Employees.city colvalue, COUNT(Add_Employees.employee_uniqueId) AS total
	  FROM dbo.Add_Employees
	  GROUP BY(Add_Employees.city

)

[mmazeemahmad]

The problem is i have different number of columns and of different data type

[urtrivedi]

use null value as place holder

select t1col1 as col1, t1col2 as col2, t1col3 as col3 from t1
union
select null as col1, t2col2 as col2, t2col3 as col3 from t2
union
select t3col1 as col1, t3col2 as col2, null as col3 from t3

[mmazeemahmad]

When i execute my queries than first Select statement returns the result as

totalEmployees

7

and the second one returns

gender ------------------- AccGender

male -------------------------8

female ---------------------- 10

But i need to get the result in one table i.e

totalEmployees --------- gender -------- AccGender

--------7 --------- male ---------- 8 etc
DID my above scenario is possible with respect to your comments??????

[adam_k]

So would Accdepartment be the next column ? And where would the female ones be shown?
Does this look right:

totalEmployees --------- gender -------- AccGender --------- Department ---------Accdepartment

--------7 --------- male ---------- 8 ----------IT Dept --------- 5
--------7 --------- female ---------- 10 ---------- IT Dept --------- 3
--------7 --------- male ---------- 2 ----------Finance --------- 1
--------7 --------- female ---------- 3 ---------- Finance --------- 3

[mmazeemahmad]

Yes definitely i need this sort of thing of what you have provided

[adam_k]

I'm assuming that you want to analyse how many employees are male and how many of them (males) belong to IT and how many IT male employees are designated X and then how many X designated IT male employees are of Grade A, etc.
Otherwise this won't make any sense.
Can you provide hierarchy? (first gender, then department, then what?)

[mmazeemahmad]

than they are distributed in the basis of grades and designations

[adam_k]

I haven't tried this and it's based on the order you've provided in your original post.
Hope this gives you an idea:

select count(*) from dbo.Add_Employees 
full outer join (
 SELECT  dbo.Add_Employees.gender, COUNT(Add_Employees.employee_uniqueId) AS AccGender
	  FROM dbo.Add_Employees
	  GROUP BY(Add_Employees.gender) ) gender 
 
left outer join (  SELECT Add_Employees.gender, dbo.Add_Employees.department, COUNT(Add_Employees.employee_uniqueId) AS Accdepartment
	  FROM dbo.Add_Employees
	  GROUP BY(Add_Employees.gender,Add_Employees.department) ) dept 
on gender.gender = dept.gender
 left outer join ( 
	  SELECT  Add_Employees.gender,dbo.Add_Employees.department, dbo.Add_Employees.designation, COUNT(Add_Employees.employee_uniqueId) AS Accdesignation
	  FROM dbo.Add_Employees
	  GROUP BY(Add_Employees.gender, dbo.Add_Employees.department, Add_Employees.designation) designation 
on dept.gender = designation.gender 
and dept.department = designation.department
left outer join ( 
	  SELECT Add_Employees.gender,  dbo.Add_Employees.department, dbo.Add_Employees.designation, dbo.Add_Employees.grade, COUNT(Add_Employees.employee_uniqueId) AS AccGrade
	  FROM dbo.Add_Employees
	  GROUP BY(Add_Employees.gender, dbo.Add_Employees.department, dbo.Add_Employees.designation, Add_Employees.grade) ) grade 
on designation.gender = grade.gender 
and designation.department = grade.department
left outer join ( 
	  SELECT Add_Employees.gender,  dbo.Add_Employees.department, dbo.Add_Employees.designation, dbo.Add_Employees.grade, dbo.Add_Employees.country, COUNT(Add_Employees.employee_uniqueId) AS AccCountry
	  FROM dbo.Add_Employees
	  GROUP BY(Add_Employees.gender,  dbo.Add_Employees.department, dbo.Add_Employees.designation, dbo.Add_Employees.grade, Add_Employees.country) ) country 
on grade.gender = country.gender 
and grade.department = country.department
and grade.designation = country.designation
and grade.grade = country.grade
left outer join  (	  
SELECT Add_Employees.gender,  dbo.Add_Employees.department, dbo.Add_Employees.designation, dbo.Add_Employees.grade, Add_Employees.country, dbo.Add_Employees.city, COUNT(Add_Employees.employee_uniqueId) AS AccCity
	  FROM dbo.Add_Employees
	  GROUP BY(Add_Employees.gender, dbo.Add_Employees.department, dbo.Add_Employees.designation, dbo.Add_Employees.grade, Add_Employees.country, Add_Employees.city)) city 
on country.gender = city.gender 
and country.department = city.department
and country.designation = city.designation
and country.grade = city.grade 
and country.country = city.country

[adam_k]

I almost forgot: You don't need distinct when you're aggregating + grouping by.

[mmazeemahmad]

when i try to execute your code it returns the syntactic error that Incorrect syntax near END but i have written corrctly

[adam_k]

I forgot to include a closing parenthesis here:

GROUP BY(Add_Employees.gender, dbo.Add_Employees.department, Add_Employees.designation) designation

it should be

GROUP BY(Add_Employees.gender, dbo.Add_Employees.department, Add_Employees.designation) ) designation