sql statement get count of deffrent types in same tabe ?

Question

1.00/5 (1 vote)

See more:

hello
i have a table name Students
and Courses and marks
i need to get the students name as show below and the courses name and get the avarage of
each course

i need sql statment to get the result as

student Name    |  Math     | English | art
Mike            |  90       | 50      | 88
ali             |  99       | 88      | 69

Posted 8-Apr-14 7:12am

sam9787

Updated 8-Apr-14 7:21am

Maciej Los

v2

Add a Solution

Comments

Guruprasad.K.Basavaraju 8-Apr-14 13:20pm

could you please post your table structure ? You can use pivoting for this if the marks are stored in rows.

sam9787 8-Apr-14 13:31pm

the table Name Students , it contains columns ( studentName , CourseName , firstexam , secoundexam , finalexam )
so i need to display the studentsName as first column , then CourseName as HeadRow , and under each course calculate the sum of three exams

Maciej Los 8-Apr-14 13:22pm

What's the input?

sam9787 8-Apr-14 13:26pm

the input as shown in the table output example ...
i just want the sql statement type and i will change the input later

Guruprasad.K.Basavaraju 8-Apr-14 13:37pm

Check the solution below.. thats just a rough idea of how to build a pivot.. modify it according to your table structure

3 solutions

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)

Guruprasad.K.Basavaraju · Answer 1 · 2014-04-08T07:37:00

Solution 2

You should query something like this

SQL

SELECT 'StudentName' AS Name,
[Math], [Art], [English]
FROM
(SELECT Course.Course_name as Course, Marks
    FROM Marks JOIN Course ON Marks.CourseId = Course.CourseId JOIN Student on Marks.StudentID= Student.ID) AS SourceTable
PIVOT
(
SUM(Marks)
FOR Course IN ([Math], [Art], [English])
) AS PivotTable;

Posted 8-Apr-14 7:37am

Guruprasad.K.Basavaraju

Comments

sam9787 8-Apr-14 15:58pm

please if these all at same table ? how can i do it ?

Guruprasad.K.Basavaraju 8-Apr-14 16:20pm

see below for solution 3.. that solution should work.. accept the answer if it solves your issue.. Thanks!!

Guruprasad.K.Basavaraju · Answer 2 · 2014-04-08T10:19:00

Here is a query if all columns are from same table..

SQL

SELECT Name,
[Math], [Art], [English]
FROM
(SELECT Name,Course, Marks
    FROM mTab ) AS SourceTable
PIVOT
(
SUM(Marks)
FOR Course IN ([Math], [Art], [English])
) AS PivotTable;

I tested with a CTE (temp table with some values) try it yourself below..

SQL

;With mTab as
(
SELECT'Guru' AS Name, 'Math' AS Course, '100' AS Marks
UNION
SELECT'Guru' AS Name, 'Art' AS Course, 90 AS Marks  UNION
SELECT'Guru' AS Name, 'English' AS Course, 80 AS Marks  UNION
SELECT'Prasad' AS Name, 'Math' AS Course, 70 AS Marks  UNION
SELECT'Prasad' AS Name, 'Art' AS Course, 60 AS Marks  UNION
SELECT'Prasad' AS Name, 'English' AS Course, 65 AS Marks  UNION
SELECT'Mike' AS Name, 'Math' AS Course, 55 AS Marks  UNION
SELECT'Mike' AS Name, 'Art' AS Course, 50 AS Marks  UNION
SELECT'Mike' AS Name, 'English' AS Course, 45 AS Marks  UNION
SELECT'Ali' AS Name, 'Math' AS Course, 40 AS Marks  UNION
SELECT'Ali' AS Name, 'Art' AS Course, 35 AS Marks  UNION
SELECT'Ali' AS Name, 'English' AS Course, 20 AS Marks
)
SELECT Name,
[Math], [Art], [English]
FROM
(SELECT Name,Course, Marks
    FROM mTab ) AS SourceTable
PIVOT
(
SUM(Marks)
FOR Course IN ([Math], [Art], [English])
) AS PivotTable;

your Output will be something like below

CSS

Name	Math	        Art		English
Ali	40		35		20
Guru	100		90		80
Mike	55		50		45
Prasad	70		60		65

Maciej Los · Answer 3 · 2014-04-08T07:24:00

Solution 1

If you want to get result as you described, you need to use PIVOT[^].

Other resources:
Pivot and Unpivot table in SQL SERVER[^]
Simple Way To Use Pivot In SQL Query[^]

Posted 8-Apr-14 7:24am