SELECT, GROUP BY & HAVING problem!

This is my TABLE

ID_1 ID_2 DAY MONTH
-----------------------------------------------
1 1 MON JAN
2 1 MON JAN
3 2 TUE FEB
4 2 TUE MAR

This is my SELECT STATEMENT

SELECT ID_2, DAY, COUNT(*) AS COUNT FROM `TABLE` GROUP BY `ID_2`,
`DAY` HAVING COUNT > 1

This RETURNS

ID_2 DAY COUNT
----------------------------------
1 MON 2
2 TUE 2

However, what I want to do is only return when there are multiple rows
(more than one) with ID_2 and DAY that match but where the MONTH is
different i.e. RETURN

ID_2 DAY COUNT
-----------------------------------
2 TUE 2

Any ideas?

Thanks in advance

stockton wrote:
> This is my TABLE
>
> ID_1 ID_2 DAY MONTH
> -----------------------------------------------
> 1 1 MON JAN
> 2 1 MON JAN
> 3 2 TUE FEB
> 4 2 TUE MAR
>
> This is my SELECT STATEMENT
>
> SELECT ID_2, DAY, COUNT(*) AS COUNT FROM `TABLE` GROUP BY `ID_2`,
> `DAY` HAVING COUNT > 1
>
> This RETURNS
>
> ID_2 DAY COUNT
> ----------------------------------
> 1 MON 2
> 2 TUE 2
>
> However, what I want to do is only return when there are multiple rows
> (more than one) with ID_2 and DAY that match but where the MONTH is
> different i.e. RETURN
>
> ID_2 DAY COUNT
> -----------------------------------
> 2 TUE 2
>
> Any ideas?
>
> Thanks in advance
>

Not tested:

SELECT ID_2, DAY, COUNT(*) AS COUNT, DISTINCT(MONTH) FROM `TABLE` GROUP
BY `ID_2`, `DAY` HAVING COUNT > 1


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

> Not tested:

> SELECT ID_2, DAY, COUNT(*) AS COUNT, DISTINCT(MONTH) FROM `TABLE` GROUP
> BY `ID_2`, `DAY` HAVING COUNT > 1


Unfortunately this doesn't work!

Any other ideas?

On 29 Feb, 06:36, stockton <simon.stock...@baesystems.com> wrote:
> > Not tested:
> > SELECT ID_2, DAY, COUNT(*) AS COUNT, DISTINCT(MONTH) FROM `TABLE` GROUP
> > BY `ID_2`, `DAY` HAVING COUNT > 1
>
> Unfortunately this doesn't work!
>
> Any other ideas?

You don't deserve more help when you post things like "this doesn't
work" without explaining precisely what doesn't work with it. But I'm
feeling generous today.

Do you actually need the count, or was it only there to detect the
multiple rows?

stockton <simon.stockton@baesystems.com> wrote in
<cbc1feba-429e-435f-a589-4654ffc0771b@u69g2000hse.googlegroups.com>:
>> SELECT ID_2, DAY, COUNT(*) AS COUNT, DISTINCT(MONTH) FROM
>> `TABLE` GROUP BY `ID_2`, `DAY` HAVING COUNT > 1
>
> Unfortunately this doesn't work!

Jerry was probably just a bit low on caffeine or something:

SELECT ID_2, DAY, COUNT(DISTINCT MONTH) AS COUNT FROM
`TABLE` GROUP BY `ID_2`, `DAY` HAVING COUNT > 1;

But really, you should've been able to figure that out
yourself. That's what the docs are for.

--
In Soviet Russia, XML documents transform *you*.

Pavel Lepin wrote:
> stockton <simon.stockton@baesystems.com> wrote in
> <cbc1feba-429e-435f-a589-4654ffc0771b@u69g2000hse.googlegroups.com>:
>>> SELECT ID_2, DAY, COUNT(*) AS COUNT, DISTINCT(MONTH) FROM
>>> `TABLE` GROUP BY `ID_2`, `DAY` HAVING COUNT > 1
>> Unfortunately this doesn't work!
>
> Jerry was probably just a bit low on caffeine or something:
>
> SELECT ID_2, DAY, COUNT(DISTINCT MONTH) AS COUNT FROM
> `TABLE` GROUP BY `ID_2`, `DAY` HAVING COUNT > 1;
>
> But really, you should've been able to figure that out
> yourself. That's what the docs are for.
>

No, wrong time of the day. Shouldn't do it shortly before bedtime :-)

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 29 Feb, 11:06, Pavel Lepin <p.le...@ctncorp.com> wrote:
> stockton <simon.stock...@baesystems.com> wrote in
> <cbc1feba-429e-435f-a589-4654ffc07...@u69g2000hse.googlegroups.com>:
>
> >> SELECT ID_2, DAY, COUNT(*) AS COUNT, DISTINCT(MONTH) FROM
> >> `TABLE` GROUP BY `ID_2`, `DAY` HAVING COUNT > 1
>
> > Unfortunately this doesn't work!
>
> Jerry was probably just a bit low on caffeine or something:
>
> SELECT ID_2, DAY, COUNT(DISTINCT MONTH) AS COUNT FROM
> `TABLE` GROUP BY `ID_2`, `DAY` HAVING COUNT > 1;
>
> But really, you should've been able to figure that out
> yourself. That's what the docs are for.
>
> --
> In Soviet Russia, XML documents transform *you*.

Here's a JOIN version for use if the count isn't actually required:
SELECT DISTINCT
i1.id_2,
i1.day
FROM table i1
JOIN table i2 ON i1.id_2 = i2.id_2 AND i1.day = i2.day AND i1.month <>
i2.month

thanks