Click here to Skip to main content
13,095,921 members (54,190 online)
Rate this:
 
Please Sign up or sign in to 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
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 13-Nov-11 19:56pm
Updated 13-Nov-11 22:45pm
Dalek Dave433.3K
v3
Comments
Dalek Dave 14-Nov-11 4:45am
   
Edited for Grammar and Readability.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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

[Edit]
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)
  Permalink  
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

I made the table and inserted sample data in SSMS and ran this query. After execution I got your desired output :

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 ?
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
v2

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web04 | 2.8.170813.1 | Last Updated 14 Nov 2011
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100