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 – a_horse_with_no_name Sep 6 '14 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 Sep 6 '14 at 8:05
  • UNION ALL would work, concat some text if you want to know which is which. – Mihai Sep 6 '14 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 Sep 6 '14 at 9:06
  • 1
    Sample data and expected output will make it easier to understand what you are locking for. – Mikael Eriksson Sep 6 '14 at 9:11
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 Sep 6 '14 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 Sep 6 '14 at 11:24
  • @Dchauhan just add a column that counts all rows. count(*) as Total. – Mikael Eriksson Sep 6 '14 at 11:28
  • done..its working like a charm – DChauhan Sep 6 '14 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 Sep 6 '14 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 Sep 6 '14 at 10:46
  • @DChauhan have a look now I have updated my answer – M.Ali Sep 6 '14 at 10:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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