[Resolved] GROUP BY IF(type is NULL, id, type)

Dr_Flink

In my db, I have a column named 'type'. The column rows can have the value A, B, D or O. If the value is A, B or D, I only want one row from the table with that value being shown. But if it has value O, I want all of the rows to be shown (within 30 days).
My code looks like this now:

$hamta = "SELECT * FROM almanacka WHERE date BETWEEN NOW() AND NOW() + INTERVAL 30 DAY GROUP BY IF(type is NULL, id, type) ORDER BY date ASC";
$resultat = mysql_query($hamta) 
or die("Det gick inte att hämta information från databasen!");

The problem is that also if type is O, only one row is shown on the page.
Someone ho could help me?

mrhappiness

O is not NULL

in case you meant 0 instead of O:
0 is not NULL

Dr_Flink

...no, I ment O (like in Omega)

amchargue

Are you saying all rows would have the same value in the type column? I don't get it.

liquorvicar

how about:

$hamta = "SELECT * FROM almanacka WHERE date BETWEEN NOW() AND NOW() + INTERVAL 30 DAY GROUP BY IF(type='O', id, type) ORDER BY date ASC";
$resultat = mysql_query($hamta) 
or die("Det gick inte att hämta information från databasen!");

Dr_Flink

Yes! That made the trick =)

Thank u all, especially liquorvicar 😃