Grouping query results where values are not null

Question

I have a query that recreates the row names into column names on the second query. Ideally will have two queries by using temporary tables are implementation. I have the first query like this:

select * from scores;

Giving me this result

EmpID  Class    Amount
1      First    200
1      Second   300
1      Third    400

Desired results:

EmpID  First    Second   Third
1      200      300       400

Query tried so far

select EmpID,
(case when p.DeductionName like '%First' then Amount else null end) as First, 
(case when p.DeductionName like '%Second' then Amount else null end) as Second,
(case when p.DeductionName like '%Third' then Amount else null end) as Third
from scores;

This one gives such a result

EmpID  First    Second   Third
1      200      null      null
1      null     300       null
1      null     null       400

Am now struggling on how I can get the desired output i.e.

EmpID  First    Second   Third
1      200      300       400

Zaynul Abadin Tuhin · Accepted Answer · 2019-09-24 08:24:38Z

4

use conditional aggregation

select EmpID,
max(case when p.DeductionName like '%First' then Amount end) as First, 
max(case when p.DeductionName like '%Second' then Amount  end) as Second,
max(case when p.DeductionName like '%Third' then Amount  end) as Third
from scores group by EmpID

answered Sep 24, 2019 at 8:24

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

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

Comments

Ed Bangga · Accepted Answer · 2019-09-24 08:28:20Z

0

Here's your query.

select t1.EmpID,
   sum(coalesce(t1.Amount, 0)) as First,
   sum(coalesce(t2.Amount, 0)) as Second,
   sum(coalesce(t3.Amount, 0)) as Third
from scores t1
left join scores t2 on t2.EmpId = t1.EmpId and t2.DeductionName like '%Second'
left join scores t3 on t3.EmpId = t1.EmpId and t3.DeductionName like '%Third'
group by t1.EmpId

answered Sep 24, 2019 at 8:28

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Comments

Mahdi Rahimi · Accepted Answer · 2019-09-24 08:42:02Z

0

You can use pivot command in sql server

SELECT *
FROM
(
    SELECT F.ID,F.RK,F.VAL
    FROM 
    (
        VALUES('1','First ',200),
        ('1','Second',300),
        ('1','Third ',400)
    )F(ID,RK,VAL)
)AS D
PIVOT(SUM(D.VAL) FOR D.RK IN ([First],[Second],[Third]))
AS PVT

answered Sep 24, 2019 at 8:42

Mahdi Rahimi

5746 silver badges22 bronze badges

2 Comments

jarlh Over a year ago

OP is using MySQL-

jarlh Over a year ago

The <sql> tag stands for the SQL language, not the MS SQL Server product. Different products have different SQL implementations. The functionality you use here isn't supported by MySQL (AFAIK.)

Collectives™ on Stack Overflow

Grouping query results where values are not null

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related