How to display sql query in a specific format

Question

I am new to SQL. I am trying to get my query to display in the format below:

FacultyName          Book     Book chapter   Journal Article     Conference
CLM                   5            11              5                 1
Health Sciences       1            0               0                 0
Humanities            1            0               0                 0
Science               0            0               0                 3

At the moment, this is how it is displayed:

FacultyName              Type            CountOfSubmissionID
CLM                      Book                     5
CLM                      Book chapter             11
CLM                      Conference               1
CLM                      Journal article          5
Health Sciences          Book                     1
Humanities               Book                     1
Science                  Conference               3

This the query that got me this:

SELECT Faculty.FacultyName,
       Submission.Type,
       Count(Submission.SubmissionID) AS CountOfSubmissionID
FROM Submission
  INNER JOIN ((Faculty INNER JOIN School ON Faculty.FacultyID = School.[Faculty ID])
    INNER JOIN (Researcher INNER JOIN ResearcherSubmission
                  ON Researcher.ResearcherID = ResearcherSubmission.ResearcherID)
        ON School.SchoolID = Researcher.SchoolID)
      ON Submission.SubmissionID = ResearcherSubmission.SubmissionID
GROUP BY Faculty.FacultyName, Submission.Type

Here are my tables:

Please advice on how I can get my query to display in the format of the first table I have drawn up.

First of all, don't tag products not involved. Are you really using both MySQL and MS SQL Server? — jarlh
– jarlh, Commented Sep 24, 2015 at 11:47
Post the table structure here in SO, many people doesn't have dropbox access from offsite-onsite locations. I guess you should try PIVOT for your case. — Abhishek
– Abhishek, Commented Sep 24, 2015 at 11:47
i hope this will help you stackoverflow.com/questions/32334486/… — wiretext
– wiretext, Commented Sep 24, 2015 at 11:53

David Isla · Accepted Answer · 2015-09-24 11:57:09Z

4

You can use CASE. Try this sentence in your code:

 SELECT Faculty.FacultyName, 
        SUM( CASE WHEN Submission.Type='Book' THEN 1 ELSE 0 END) Book,
        SUM( CASE WHEN Submission.Type='Book Chapter' THEN 1 ELSE 0 END) BookChapter,
        SUM( CASE WHEN Submission.Type='Conference' THEN 1 ELSE 0 END) Conference
 FROM Submission INNER JOIN ((Faculty INNER JOIN School ON Faculty.FacultyID = School.[Faculty ID]) INNER JOIN (Researcher INNER JOIN ResearcherSubmission ON Researcher.ResearcherID = ResearcherSubmission.ResearcherID) ON School.SchoolID = Researcher.SchoolID) ON Submission.SubmissionID = ResearcherSubmission.SubmissionID
 GROUP BY Faculty.FacultyName, Submission.Type;

Good Luck!

answered Sep 24, 2015 at 11:57

David Isla

6297 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kivan Ilangakoon Over a year ago

thanks, this works for me. however it doesn't group the faculty. eg: in the case of CLM, it displays it in 4 different rows, (each row showing data for Book, Book Chapter, Journal and Conference, independently). I appreciate your assistance though.

David Isla Over a year ago

Sorry kivan!, remove submission.Type from group by. I am on my phone now

Collectives™ on Stack Overflow

How to display sql query in a specific format

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related