1
SELECT distinct Major, COUNT(Major) AS MajorCount FROM 
[College Management System].[dbo].[ApplicantCourseDetails]
group by Major 

SELECT distinct MinorI, COUNT(MinorI) AS MinorCount FROM 
[College Management System].[dbo].[ApplicantCourseDetails]
group by MinorI

Result should be like this.

╔═══════════╦════════════╦════════════╦════════════╗
║   Major   ║ MajorCount ║   MinorI   ║ MinorCount ║
╠═══════════╬════════════╬════════════╬════════════╣
║ Botany    ║ 1          ║            ║            ║
║ Chemistry ║ 2          ║ Chemistry  ║ 2          ║
║ English   ║ 2          ║ English    ║ 2          ║
║ History   ║ 2          ║ History    ║ 1          ║
║           ║            ║ Philosophy ║ 1          ║
║           ║            ║ Zoology    ║ 1          ║
╚═══════════╩════════════╩════════════╩════════════╝
10
  • Added sql-server tag based on the non-standard syntax
    – user1822
    Commented Sep 6, 2014 at 7:51
  • both of these query are giving me the result i want..all i want is, write only 1 query instead of two select query
    – DChauhan
    Commented Sep 6, 2014 at 8:05
  • UNION ALL would work, concat some text if you want to know which is which.
    – Mihai
    Commented Sep 6, 2014 at 8:53
  • union all is not giving me the output i want..sub query might give me the right output..but how to write a sub query for this???
    – DChauhan
    Commented Sep 6, 2014 at 9:06
  • 1
    Sample data and expected output will make it easier to understand what you are locking for. Commented Sep 6, 2014 at 9:11

2 Answers 2

4

You can use a cross apply and values to create a row for each value and an extra column in the values clause to keep track of what value is a major and what value is a minor.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE ApplicantCourseDetail(
    [Major] [nvarchar](50) NULL,
    [MinorI] [nvarchar](50) NULL
)
INSERT INTO ApplicantCourseDetail
           ([Major]
           ,[MinorI])
     VALUES
           ('Chemistry','History'),
('English','Chemistry'),
('Botany','Zoology'),
('History','English'),
('Chemistry','Chemistry'),
('English','Philosophy'),
('History','English')

Query 1:

select T.Value,
       count(case when T.IsMajor = 1 then A.Major end) as MajorCount,
       count(case when T.IsMajor = 0 then A.MinorI end) as MinorCount
from dbo.ApplicantCourseDetail as A
  cross apply (values (A.Major, 1),(A.MinorI, 0)) as T(Value, IsMajor)
group by T.Value;

Results:

|      VALUE | MAJORCOUNT | MINORCOUNT |
|------------|------------|------------|
|     Botany |          1 |          0 |
|  Chemistry |          2 |          2 |
|    English |          2 |          2 |
|    History |          2 |          1 |
| Philosophy |          0 |          1 |
|    Zoology |          0 |          1 |
4
  • much much better result..thanks Mikael Eriksson
    – DChauhan
    Commented Sep 6, 2014 at 11:13
  • mikael i also want to add more column in output Total, in which the sum of majorcount and minor count ll displayed..Eg. Botany total should be 1, chemistry total ll be 4 n so on..
    – DChauhan
    Commented Sep 6, 2014 at 11:24
  • @Dchauhan just add a column that counts all rows. count(*) as Total. Commented Sep 6, 2014 at 11:28
  • done..its working like a charm
    – DChauhan
    Commented Sep 6, 2014 at 11:33
2
SELECT * 
FROM
(SELECT  Major, COUNT(Major) AS MajorCount 
FROM #ApplicantCourseDetail
group by Major) A 
FULL JOIN 
(SELECT MinorI, COUNT(MinorI) AS MinorCount 
FROM #ApplicantCourseDetail
group by MinorI)B
ON A.Major = B.MinorI

Result

╔═══════════╦════════════╦════════════╦════════════╗
║   Major   ║ MajorCount ║   MinorI   ║ MinorCount ║
╠═══════════╬════════════╬════════════╬════════════╣
║ Botany    ║ 1          ║ NULL       ║ NULL       ║
║ Chemistry ║ 2          ║ Chemistry  ║ 2          ║
║ English   ║ 2          ║ English    ║ 2          ║
║ History   ║ 2          ║ History    ║ 1          ║
║ NULL      ║ NULL       ║ Philosophy ║ 1          ║
║ NULL      ║ NULL       ║ Zoology    ║ 1          ║
╚═══════════╩════════════╩════════════╩════════════╝

Update

SELECT ISNULL(Major, '')       AS Major
      ,ISNULL(CAST(MajorCount AS VARCHAR(10)), '') AS MajorCount 
      ,ISNULL(MinorI , '')     AS MinorI
      ,ISNULL(CAST(MinorCount AS VARCHAR(10)), '') AS MinorCount
 FROM
(SELECT  Major, COUNT(Major) AS MajorCount 
FROM #ApplicantCourseDetail
group by Major) A 
FULL JOIN 
(SELECT MinorI, COUNT(MinorI) AS MinorCount 
FROM #ApplicantCourseDetail
group by MinorI)B
ON A.Major = B.MinorI

Result

╔═══════════╦════════════╦════════════╦════════════╗
║   Major   ║ MajorCount ║   MinorI   ║ MinorCount ║
╠═══════════╬════════════╬════════════╬════════════╣
║ Botany    ║          1 ║            ║            ║
║ Chemistry ║          2 ║ Chemistry  ║          2 ║
║ English   ║          2 ║ English    ║          2 ║
║ History   ║          2 ║ History    ║          1 ║
║           ║            ║ Philosophy ║          1 ║
║           ║            ║ Zoology    ║          1 ║
╚═══════════╩════════════╩════════════╩════════════╝
3
  • this is what i'm lookin for.. but how can i avoid these null values
    – DChauhan
    Commented Sep 6, 2014 at 10:29
  • You cannot avoid nulls as Both queries return different values and getting them in together in same result set will result in some null values.
    – M.Ali
    Commented Sep 6, 2014 at 10:46
  • @DChauhan have a look now I have updated my answer
    – M.Ali
    Commented Sep 6, 2014 at 10:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.