CODEWITHSUNDEEP
mysqlselectgroup-by
Lars Lyngsøe Højberg
Lars Lyngsøe Højberg

Reputation: 75

Group rows but keep values where not null

I am trying to group rows in MySQL but end up with a wrong result.

My DB looks like this: ScreenDump

I'm using this query:

SELECT 
    r_id, va_id,va_klasse,va_periode, 
    1va_mer,1va_hjem,1va_mot,1va_bil,1va_fit,1va_hand,1va_med,1va_fra, 
    2va_mer,2va_hjem,2va_trae,2va_bil,2va_sty,2va_mus,2va_med,2va_fra,
    3va_mer,3va_hjem,3va_mot,3va_bil,3va_pima,3va_nat,3va_med,3va_fra,
    va_lock, va_update

FROM o6hxd_valgfag
WHERE va_klasse IN('7A','7B','7C','8A','8B','8C','9A','9B','9C')
GROUP BY va_id
ORDER BY va_klasse,va_name

This produces a wrong result, where one row is returned with only the first three numbers 123 and not the ones from row two and three. What I would like is a result where the numbers 123, 321 and 132 are gathered in one line.

I can explain more detailed if this isn't sufficient.

Upvotes: 1

Views: 57

Answers (3)

raidenace
raidenace

Reputation: 12826

Your query will not work as intended. Think about this use-case:

what if for row1 (r_id =9), the fields 2va_sty, 2va_mus, 2va_med are not empty and has values?

In such case what should your desired output be? It certainly cannot be the numbers 123, 321 and 132 gathered in one line. Group by is usually used if you want to use aggregate functions executed against a certain field value, in your case va_id.

Upvotes: 1

Paul Stanley
Paul Stanley

Reputation: 4098

If across those fields there should only be ever one value, you should really have them all in the same record and go about fixing it to insert and update the same record.

Ie I am aware that you database isn't designed correctly However To dig you out, you could give this a crack, I suppose.

SELECT 
    r_id, va_id,va_klasse,va_periode, 
    MAX(1va_mer),MAX(1va_hjem),MAX(1va_mot),MAX(1va_bil),MAX(1va_fit),MAX(1va_hand),MAX(1va_med),MAX(1va_fra), 
    MAX(2va_mer),MAX(2va_hjem),MAX(2va_trae),MAX(2va_bil),MAX(2va_sty),MAX(2va_mus),MAX(2va_med),MAX(2va_fra),
    MAX(3va_mer),MAX(3va_hjem),MAX(3va_mot),MAX(3va_bil),MAX(3va_pima),MAX(3va_nat),MAX(3va_med),MAX(3va_fra),
    va_lock, va_update

FROM o6hxd_valgfag
WHERE va_klasse IN('7A','7B','7C','8A','8B','8C','9A','9B','9C')
GROUP BY va_id
ORDER BY va_klasse,va_name

Upvotes: 2

Kostas Mitsarakis
Kostas Mitsarakis

Reputation: 4747

Not a solution to your problem but i think a better query would be like this (because of the not named columns in the group by clause https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html):

SELECT 
    aa.r_id, aa.va_id, aa.va_klasse, aa.va_periode, 
    aa.1va_mer, aa.1va_hjem, aa.1va_mot, aa.1va_bil, aa.1va_fit, aa.1va_hand, aa.1va_med, aa.1va_fra, 
    aa.2va_mer, aa.2va_hjem, aa.2va_trae, aa.2va_bil, aa.2va_sty,2va_mus, aa.2va_med, aa.2va_fra,
    aa.3va_mer, aa.3va_hjem, aa.3va_mot, aa.3va_bil, aa.3va_pima, aa.3va_nat, aa.3va_med, aa.3va_fra,
    aa.va_lock, aa.va_update
FROM o6hxd_valgfag AS aa
INNER JOIN (
    SELECT va_id
    FROM o6hxd_valgfag
    GROUP BY va_id
) AS _aa
ON aa.va_id = _aa.va_id
WHERE aa.va_klasse IN ('7A','7B','7C','8A','8B','8C','9A','9B','9C')
ORDER BY aa.va_klasse, aa.va_name;

Upvotes: 0

Related Questions