CODEWITHSUNDEEP
mysqlsql
Aashish sharma
Aashish sharma

Reputation: 1

How to join THREE different SQL queries having common column

1st Query:

select MONTHNAME(batchStartDate) AS MONTHS,
    MONTH(batchStartDate) AS MONTHNUMBER,
    SUM(totalCandidatesInBatch) AS CANDIDATES_ENROLLED 
from batch 
group by MONTHS 
order by MONTHNUMBER;

2nd Query:

select MONTHNAME(resultApprovedOndate) AS MONTHS,
    MONTH(resultApprovedOndate) AS MONTHNUMBER,
    SUM(totalCertified) AS CANDIDATES_CERTIFIED 
from batch 
where totalPass is not null AND 
    totalFail is not null AND 
    resultApprovedOnDate<=curDate() and 
    resultApprovedOnDate is not null 
group by MONTHS 
order by MONTHNUMBER;

3rd Query:

select MONTHNAME(assessmentDate) AS MONTHS,
    MONTH(assessmentDate) AS MONTHNUMBER,
    (SUM(totalPass)+SUM(totalFail)) AS CANDIDATES_ASSESSED 
from batch 
where totalPass is not null AND 
    totalFail is not null AND 
    resultApprovedOnDate<=curDate() and 
    resultApprovedOnDate is not null 
group by MONTHS 
order by MONTHNUMBER;

As you can see these Queries have common column MONTHS and MONTHNUMBER , I want to join the result of these three queries. I have tried UNION but that doesn't work.

Can anyone tell me what's happening and what could be the solution?

OUTPUT 1st QUERY

 # MONTHS, MONTHNUMBER, CANDIDATES_ENROLLED
 January,       1,       50
 February,      2,       35
 March,         3,       205
 April,         4,       85
 May,           5,       616
 June,          6,       26

2nd QUERY

 # MONTHS, MONTHNUMBER, CANDIDATES_CERTIFIED
  January,       1,            34
    April,       4,            72
     July,       7,            484
   August,       8,            30

3rd QUERY

 # MONTHS, MONTHNUMBER, CANDIDATES_ASSESSED
 February,       2,           101
    March,       3,           134
      May,       5,           32
     July,       7,           75
   August,       8,           75
September,       9,           150
  October,       10,           75
 November,       11,           77
 December,       12,          152

I want the output to be displayed in a form

# MONTHS, MONTHNUMBER, CANDIDATES_ENROLLED, CANDIDATES_CERTIFIED, CANDIDATES_ASSESSED

and also if a table has a Row Missing then the value should be filled with zero or null.Ex- In 3rd Query Output January is missing then candidates assessed should be 0 or null.

Upvotes: 0

Views: 69

Answers (2)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

I think this can be done with a single query:

select MONTHNAME(batchStartDate) AS `MONTHS`,
    MONTH(batchStartDate) AS `MONTHNUMBER`,
    SUM(totalCandidatesInBatch) AS `CANDIDATESE_ENROLLED`,
    SUM(
        IF(NOT(totalFail IS NULL) AND 
            NOT(totalFail IS NULL) AND 
            resultApprovedOnDate <= curDate() AND 
            NOT(resultApprovedOnDate is null),
            totalCertified,
            0
        ) AS `CANDIDATESE_CERTIFIED`,
    SUM(
        IF(NOT(totalFail IS NULL) AND 
            NOT(totalFail IS NULL) AND 
            resultApprovedOnDate <= curDate() AND 
            NOT(resultApprovedOnDate is null),
            totalPass + totalFail,
            0
        ) AS `CANDIDATESE_ASSESSED`
from batch 
group by MONTHS 
order by MONTHNUMBER;

Upvotes: 0

tee
tee

Reputation: 1336

Look at this link: https://www.w3schools.com/sql/sql_join.asp

It shows you the different types of joins (I'm assuming you want to do a "join").

Union is used to combine two or more sets on top of eachother.

Using join you can join based on a related column between your tables

Upvotes: 0

Related Questions