Need a query to display in a particular format

Question

I have two tables u and marklist:

SELECT * from u;
+------+-------+--------+-------+------------+
| id   | name  | adress | class | DOB        |
+------+-------+--------+-------+------------+
|    1 | Arun  | kollam |     6 | 1993-02-01 |
|    2 | Anoop | kollam |     6 | 1993-05-11 |
|    2 | Devi  | kollam |     6 | 1993-04-16 |
+------+-------+--------+-------+------------+

3 rows in set (0.02 sec)
 select * from marklist;
+------+-----------+------------+-------+--------+
| id   | subject   | markscored | outof | userid |
+------+-----------+------------+-------+--------+
|    1 | biology   |         37 |    50 |      1 |
|    2 | chemistry |         48 |    50 |      1 |
|    3 | physics   |         48 |    50 |      1 |
|    4 | biology   |         45 |    50 |      2 |
|    5 | chemistry |         41 |    50 |      2 |
|    6 | physics   |         34 |    50 |      2 |
|    7 | biology   |         39 |    50 |      3 |
|    8 | chemistry |         46 |    50 |      3 |
|    9 | physics   |         48 |    50 |      3 |
+------+-----------+------------+-------+--------+
9 rows in set (0.00 sec)

And from this two tables I require an output as:

+------+------+---------+-----------+---------+----------------+------------+
| name |class | biology | chemistry | physics | totlmarkscored | percentage |
+------+------+---------+-----------+---------+----------------+------------+

SELECT u.name,u.class, SUM(CASE WHEN subject LIKE 'biology' THEN markscored ELSE 0 END) AS biology, SUM(CASE WHEN subject LIKE 'chemistry' THEN markscored ELSE 0 END) AS chemistry, SUM(CASE WHEN subject LIKE 'physics' THEN markscored ELSE 0 END) AS physics, SUM( markscored) AS TOTAL, SUM(outof) AS OUTOF FROM u, marklist m WHERE u.id=m.userid GROUP BY u.name,u.class;by using the above query i get my required output........ — Gowrisankar, Dec 12 '16 at 11:57

score 0 · Answer 1 · answered Dec 08 '16 at 10:08

0

SELECT u.name, u.class, marklist.subject as biology, marklist.subject as chemistry, marklist.subject as physics, marklist.totlmarkscored, marklist.percentage
FROM u
INNER JOIN marklist
ON u.id=marklist.id

Feel free to edit my mistakes!

answered Dec 08 '16 at 10:08

Faizan Ali

13
7

LOL ... there is no column present named `totlmarkscored`. that's a calculated column mate – Rahul Dec 08 '16 at 10:09
he can use marksscored as totlmarkscored :p – Faizan Ali Dec 08 '16 at 10:10
Repeating `marklist.subject` in the `SELECT` list will not automatically pivot the table. – Barmar Dec 08 '16 at 10:12
thank you all for helping me..... – Gowrisankar Dec 09 '16 at 06:02
SELECT u.name,u.class, SUM(CASE WHEN subject LIKE 'biology' THEN markscored ELSE 0 END) AS biology, SUM(CASE WHEN subject LIKE 'chemistry' THEN markscored ELSE 0 END) AS chemistry, SUM(CASE WHEN subject LIKE 'physics' THEN markscored ELSE 0 END) AS physics, SUM( markscored) AS TOTAL, SUM(outof) AS OUTOF FROM u, marklist m WHERE u.id=m.userid GROUP BY u.name,u.class; – Gowrisankar Dec 12 '16 at 11:58
by using the above query i get my output – Gowrisankar Dec 12 '16 at 11:59

Tim Biegeleisen · Answer 2 · 2016-12-09T07:30:36.540

0

SELECT t1.name,
       t1.class,
       t2.biology,
       t2.chemistry,
       t2.physics,
       COALESCE(t2.biology, 0) + COALESCE(t2.chemistry, 0) +
           COALESCE(t2.physics, 0) AS totlmarkscored,
       100*(COALESCE(t2.biology, 0) + COALESCE(t2.chemistry, 0) +
           COALESCE(t2.physics, 0)) /
       (COALESCE(t2.outofbiology, 0) + COALESCE(t2.outofchemstry, 0) +
        COALESCE(t2.outofphysics, 0)) AS percentage
FROM u t1
INNER JOIN
(
    SELECT userid,
           MAX(CASE WHEN subject = 'biology'   THEN markscored END) AS biology,
           MAX(CASE WHEN subject = 'biology'   THEN outof END)      AS outofbiology,
           MAX(CASE WHEN subject = 'chemistry' THEN markscored END) AS chemistry,
           MAX(CASE WHEN subject = 'chemistry' THEN outof END)      AS outofchemistry,
           MAX(CASE WHEN subject = 'physics'   THEN markscored END) AS physics,
           MAX(CASE WHEN subject = 'physics'   THEN outof END)      AS outofphysics,
    FROM marklist
    GROUP BY userid
) t2
    ON t1.id = t2.userid

edited Dec 09 '16 at 07:30

answered Dec 08 '16 at 10:19

Tim Biegeleisen

502,043
27
286
360

thank you sir.................. – Gowrisankar Dec 08 '16 at 13:20
SIR I M GETTING AN ERROR AS: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use n ear ') AS biology, MAX(CASE WHEN subject = 'biology' THEN outof) AS' at line 16 – Gowrisankar Dec 09 '16 at 07:28
PLS REPLY........... – Gowrisankar Dec 09 '16 at 07:29
@Gowrisankar Sorry I had a few typos in there. – Tim Biegeleisen Dec 09 '16 at 07:31
could you pls correct it sir? – Gowrisankar Dec 09 '16 at 11:24
sir pls help me ............ – Gowrisankar Dec 10 '16 at 05:10

Need a query to display in a particular format

2 Answers2