Group by and Case Statements

Hi,
I have a query where I am using a case statement and a group by e.g.
Select Field1, Field2, Field3, Max(Field4),
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END as Field4
WHERE <something>
GROUP BY Field1, Field2, Field3, Field4
The problem I am having is the group by isn't using the value from
the Case Statement, it's not grouping the records correctly. Anyone
have any idea's why that would happen?
Thanks for the helpBy definition, the expressions in the GROUP BY clause reffer to the
columns from the table, not to the columns defined in the SELECT
clause. You probably need to use a subquery for this:
SELECT Field1, Field2, Field3, MAX(Field4), TheExpression as Field4
FROM (
SELECT Field1, Field2, Field3, Field4,
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END as TheExpression
FROM <your table>
WHERE <something>
) x
GROUP BY Field1, Field2, Field3, TheExpression
Razvan|||Thanks, that's what I figured. I was hoping there was a more eligant
way around that.
Thanks|||Simply copy the same CASE expression used in the SELECT list into the
GROUP BY:
Select Field1, Field2, Field3, Max(Field4),
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END as Field4
WHERE <something>
GROUP BY Field1, Field2, Field3,
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END
Roy Harvey
Beacon Falls, CT
On 3 May 2006 09:25:44 -0700, "Red2" <sdibello@.gmail.com> wrote:

>Hi,
> I have a query where I am using a case statement and a group by e.g.
> Select Field1, Field2, Field3, Max(Field4),
> CASE
> WHEN <something> THEN ' '
> WHEN <something> THEN 1
> END as Field4
> WHERE <something>
> GROUP BY Field1, Field2, Field3, Field4
> The problem I am having is the group by isn't using the value from
>the Case Statement, it's not grouping the records correctly. Anyone
>have any idea's why that would happen?
>Thanks for the help|||Red2 wrote:
> Hi,
> I have a query where I am using a case statement and a group by e.g.
> Select Field1, Field2, Field3, Max(Field4),
> CASE
> WHEN <something> THEN ' '
> WHEN <something> THEN 1
> END as Field4
> WHERE <something>
> GROUP BY Field1, Field2, Field3, Field4
> The problem I am having is the group by isn't using the value from
> the Case Statement, it's not grouping the records correctly. Anyone
> have any idea's why that would happen?
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
It'd be better if you showed the actual query. Your example has a
Max(Field4) and the CASE expression is labeled as Field4. If the GROUP
BY clause has Field4 is will be grouping the Field4 in the Max()
function. Obviously, not what you want to do. Is this a misnomer or
are you really using the same column in the Max() function and as the
CASE expression's name?
If you want to GROUP BY the result of a CASE expression you have to
include the CASE expression in the GROUP BY clause:
GROUP BY field1, field2, field3, CASE WHEN <something> THEN X ELSE Y END
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRFkUgYechKqOuFEgEQLZ2ACdHDbb0StLG/MEkaOpME4KfeDYTfcAn1I2
EEZOhQ/brevVe0gSJNujPhER
=aD2k
--END PGP SIGNATURE--