Calculate total fee of each teacher in all department

Question

1.00/5 (1 vote)

See more:

Need to calculate total fee of each teacher in all department.

Below is the sql table.

Dept	   Teacher	    Subject	     Fees per lecture
Science	   Pro.Abhay	Physics	    500
Science	   Pro Vishal	Chemistry	300
Science	   Pro Vishal	Biology	    400
Commerce   Pro.Abhay	Accountancy	600
Commerce   Pro.Abhay	Economics	400
Commerce   Pro Himanshu	English	    300
Art	       Pro.Abhay	History	    500
Art	       Pro Himanshu	Hindi	    300
Art	       Pro Himanshu	Geography	500

Output ::

Teacher	        Science	  Commerce	    Art
Pro.Abhay	    500	       1000	        500
Pro Vishal	    700	       0	        0
Pro Himanshu	0	      300	        800

What I have tried:

Output ::

Teacher	        Science	  Commerce	    Art
Pro.Abhay	    500	       1000	        500
Pro Vishal	    700	       0	        0
Pro Himanshu	0	      300	        800

Posted 14-Jan-21 9:05am

Abhijit Dhanwate

Updated 14-Jan-21 10:52am

Maciej Los

v2

Add a Solution

Comments

Maciej Los 14-Jan-21 16:47pm

Wow!
Now, we know what you want, but what have you tried till now?

2 solutions

Solution 1

We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]

Posted 14-Jan-21 9:24am

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2021-01-14T10:52:00

Solution 2

You need to use PIVOT[^]!

[UPDATE]
Try:

SQL

CREATE TABLE AAA
(
  Dept varchar(50),
  Teacher varchar(50),
  [Subject] varchar(50),
  [Fees per lecture] int
);

INSERT INTO AAA (Dept, Teacher, [Subject], [Fees per lecture])
VALUES('Science', 'Pro.Abhay', 'Physics', 500),
('Science', 'Pro Vishal', 'Chemistry', 300),
('Science', 'Pro Vishal', 'Biology', 400),
('Commerce', 'Pro.Abhay', 'Accountancy', 600),
('Commerce', 'Pro.Abhay', 'Economics', 400),
('Commerce', 'Pro Himanshu', 'English', 300),
('Art', 'Pro.Abhay', 'History', 500),
('Art', 'Pro Himanshu', 'Hindi', 300),
('Art', 'Pro Himanshu', 'Geography', 500);

SELECT Teacher, [Science], [Commerce], [Art]
FROM 
(
    SELECT Dept, Teacher, [Fees per lecture]
    FROM AAA
) dt
PIVOT(SUM([Fees per lecture]) FOR [Dept] IN([Science], [Commerce], [Art])) pvt

db<>fiddle[^]

Posted 14-Jan-21 10:52am

Maciej Los

Updated 14-Jan-21 20:30pm

v3

Comments

Abhijit Dhanwate 15-Jan-21 2:18am

Hello Thanks for your reply. But by your query I am getting below output. Which doesn't match required output.

Teacher Science Commerce Art
Pro.Ajay NULL 600 NULL
Pro Vishal 400 NULL NULL
Pro Vishal 300 NULL NULL
Pro.Ajay NULL 400 NULL
Pro.Himanshu NULL 300 NULL
Pro.Himanshu NULL NULL 500
Pro.Himanshu NULL NULL 300
Pro.Ajay NULL NULL 500
Pro.Ajay 500 NULL NULL

Maciej Los 15-Jan-21 2:29am

See updated answer.

Abhijit Dhanwate 15-Jan-21 3:28am

Achieve by below query ,Thanks for your reply.

SELECT Teacher, [Science], [Commerce], [Art]
FROM
(
SELECT Dept, Teacher, [Fee]
FROM TeacherDtl
WHERE Teacher <> ''
) dt
PIVOT(SUM([Fee]) FOR [Dept] IN([Science], [Commerce], [Art])) pvt

Maciej Los 15-Jan-21 5:07am

You're very welcome.

thatraja 15-Jan-21 4:18am

5!

Maciej Los 15-Jan-21 5:06am

Thank you.