Sql-server – How to count the rows from 1 table but from two different column

countselectsql server

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          ║
╚═══════════╩════════════╩════════════╩════════════╝

Best Answer

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 |
Related Question