Click here to Skip to main content
11,489,693 members (71,488 online)
Rate this: bad
good
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 20:56pm
Edited 13-Nov-11 23:45pm
Dalek Dave433.2K
v3
Comments
Dalek Dave at 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 at 14-Nov-11 2:12am
   
No its not working prerak..
still its showing the null value
Prerak Patel at 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 at 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 at 14-Nov-11 2:29am
   
You cannot achieve the provided output with the query in post. Check my query in answer.
Ston Cold at 14-Nov-11 4:38am
   
Thanks prerak your query did worked for me..
Ston Cold at 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 at 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
0 Sergey Alexandrovich Kryukov 535
1 OriginalGriff 223
2 Andy Lanng 193
3 Maciej Los 190
4 _duDE_ 141
0 Sergey Alexandrovich Kryukov 9,488
1 OriginalGriff 8,425
2 Sascha Lefèvre 3,344
3 Maciej Los 3,066
4 Richard Deeming 2,370


Advertise | Privacy | Mobile
Web03 | 2.8.150520.1 | Last Updated 14 Nov 2011
Copyright © CodeProject, 1999-2015
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