Re: Group By with condition

On Thu, May 21, 2015 at 3:29 PM, Steve Midgley <science(at)misuse(dot)org> wrote:

>
> On Thu, May 21, 2015 at 3:14 PM, JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>
> wrote:
>
>> Let´s suppose I have the following query:
>>
>> SELECT fld1, fld2, fld3, fld4 FROM tblTable1
>> INNER JOIN ............
>> GROUP BY fld1, fld2, fld3, fld4
>>
>> What I need is to group by "fld4" if and only if its value is "TL". If
>> its value is different than "TL" then no grouping should be performed on
>> this field.
>>
>> I will very much appreciate ypur feedback.
>>
>> ​
> ​
> I'm not totally clear on your requirement but would the HAVING keyword
> solve the problem? That's kind of like a WHERE but in the group by phase of
> the query..
>
> SELECT fld1, fld2, fld3, fld4 FROM tblTable1
> INNER JOIN ............
> GROUP BY fld1, fld2, fld3, fld4
> HAVING fld4='TL'
>

​SELECT fld1, fld2, fld3, CASE WHEN fld4 = 'TL' THEN fld4 ELSE pkcol​ END
AS fld4_or_pk
FROM/JOIN
GROUP BY 1, 2, 3, 4

You need to make sure that the value in the fourth column is unique if the
value of fld4 is anything other than 'TL'. The easiest way is to simply
use a relevant primary key as the substitute value.

You perform this case against the actual data and then reference the
columns by position in the group by.

Sample data and expected output will likely be needed should you require
further assistance; your problem specification is lacking - specifically
the expected values for fld4 and what you expect to output in the fourth
column of the final query. Also, your example does not have any actual
aggregate functions? Are you using this simply to remove duplicate rows
from the output? If so you should consider if DISTINCT ON () or a
different query altogether will perform better.

David J.