Click here to Skip to main content
14,878,268 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
I have a full join sql query and I am retrieving the data from the same table.

The problem is I am getting the null value where I am expecting the column name.

Example:
I am having a table where there are two columns typeOfPost,dob.
DOB           TypeOfPost
---------    --------------
20/11/1998     Manager
1/1/2000       Sales
13/6/1999      Manager
20/1/1987      Manager
1/11/1985      Sales


Now when I am writing a join query like
SQL
select DATENAME(month,dob) as Red,count(TypeOfPost) 
from tablename
where TypeOfPost='Manager'
group by DATENAME(month,dob) as A

full join

select DATENAME(month,dob) as Green,count(TypeOfPost) 
from tablename
where TypeOfPost='Sales'
group by DATENAME(month,dob) as B on B.Green = A.Red

Output--                                Expected Output--
---------------------                   ---------------------
Month     Man     Sal                   Month     Man     Sal
--------  -----  ------                 --------  -----  ------
January    1       1                    January    1       1
NULL       1      NULL                  June       1      NULL
November   1       1                    November   1       1


Now here the problem rise, I want 'June' in the column Month instead of NULL value.
So is there any way to get that?
Help me out.
Thanks.
Posted
Updated 13-Nov-11 22:45pm
v3
Comments
Dalek Dave 14-Nov-11 4:45am
   
Edited for Grammar and Readability.

I think you require to set date format before your query. SET DATEFORMAT dmy;
http://msdn.microsoft.com/en-us/library/ms189491.aspx[^]

[Edit]
SQL
select datename(month,dob) 'Month', sum(case when typeofpost='manager'THEN 1 ELSE 0 END) 'Man',sum(case when typeofpost='sales'THEN 1 ELSE 0 END) 'Sal'
FROM tablename
group by datename(month,dob)
   
v2
Comments
Ston Cold 14-Nov-11 2:12am
   
No its not working prerak..
still its showing the null value
Prerak Patel 14-Nov-11 2:18am
   
If you provide exact query and output, we might help you better. Your given query cannot produce this output any way. I just thought that date format could be the issue.
Ston Cold 14-Nov-11 2:26am
   
thats the exact query i have provided, dont worry about the column headings it will be different but the values are exact.
Prerak Patel 14-Nov-11 2:29am
   
You cannot achieve the provided output with the query in post. Check my query in answer.
Ston Cold 14-Nov-11 4:38am
   
Thanks prerak your query did worked for me..
Ston Cold 14-Nov-11 4:48am
   
One more question prerak if i want the count of manager and sales in other column then how would i get??

Example--
Total Count
-------------
2
1
2

in the above case.
Prerak Patel 14-Nov-11 5:38am
   
Yes, you can add those two sums in query.
I made the table and inserted sample data in SSMS and ran this query. After execution I got your desired output :

SQL
select Red month, man, sal from
(select DATENAME(month,dob) as Red,count(TypeOfPost) man
from tablename
where TypeOfPost='Manager'
group by DATENAME(month,dob) ) a
full join
(select DATENAME(month,dob) as Green,count(TypeOfPost) sal
from tablename
where TypeOfPost='Sales'
group by DATENAME(month,dob) ) b on B.Green = A.Red


output was :

month                          man         sal
------------------------------ ----------- -----------
January                        1           1
June                           1           NULL
November                       1           1


I think the result is the one you wanted :-?
Is it ok ?
   
SQL
select datename(month,dob) 'Month', sum(case when typeofpost='manager'THEN 1 ELSE 0 END) 'Man',sum(case when typeofpost='sales'THEN 1 ELSE 0 END) 'Sal'
FROM tablename
group by datename(month,dob);



Thanks to prerak patel..
Cheers
   
v2

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900