CODEWITHSUNDEEP
mysqlsql
Azima
Azima

Reputation: 4141

merge two rows with same id but different column values into one row [mysql]

I have joined two tables and obtained a resultset that has different rows for the same id or say, type, but they have different column values.

Eg. 

leave_type  |  max Days  |  jan  |  feb  |  mar  |  apr
Personal    |  12        |  0.00 |  0.00 |  2.00 |  0.00
Personal    |  12        |  1.00 |  0.00 |  0.00 |  0.00
Sick        |  5         |  0.00 |  0.00 |  1.00 |  0.00

I would like the result as follows:

leave_type  |  max Days  |  jan  |  feb  |  mar  |  apr
Personal    |  12        |  1.00 |  0.00 |  2.00 |  0.00
Sick        |  5         |  0.00 |  0.00 |  1.00 |  0.00

How can this be done in mysql?

Any help is very much appreciated. Thanks.

Upvotes: 4

Views: 12583

Answers (3)

kchason
kchason

Reputation: 2885

You can use GROUP BY and aggregate functions. 

SELECT
    `leave_type`,
    MAX(`days`) AS `max_days`,
    MAX(`jan`) AS `jan`,
    MAX(`feb`) AS `feb`,
    MAX(`mar`) AS `mar`,
    MAX(`apr`) AS `apr`
FROM
    `table`
GROUP BY
    `leave_type`

It's not clear whether you want to use MAX or SUM from your example, but you can use either, depending on your goal.

Upvotes: 7

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

Try aggregating by leave type and taking the sum of the month columns:

SELECT
    leave_type,
    MAX(`max Days`) AS max_days,
    SUM(jan) AS jan,
    SUM(feb) AS feb,
    SUM(mar) AS mar,
    SUM(apr) AS apr
FROM yourTable
GROUP BY leave_type;

It is not clear whether the max days would always be the same for a given leave type. In any case, I arbitrarily retain the maximum value.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can just use an aggregation query:

select leave_type, maxDays,
       sum(jan) as jan, sum(feb) as feb, sum(mar) as mar, sum(apr) as apr
from t
group by leave_type, maxDays ;

Upvotes: 2

Related Questions