sql statement group by?

[Guest]

hey all,

I have a group by problem in mysql made in php code

if I have this table

table TEST

colunms:

ID
SID
email
DATE

and this data

ID SID EMAIL DATE

1 3 A@ BLA
2 3 A@ BLA
3 1 S@ BLA
4 1 S@ BLA
5 5 A@ BLA
6 1 A@ BLA
7 1 A@ BLA

and i do this query


SELECT count(*) AS C, EMAIL, SID FROM TEST
WHERE SID = SID GROUP BY email ORDER BY C DESC

i get

C EMAIL SID
2 A@ 3
2 S@ 1
1 A@ 5



BUT I WANT TO GET THIS

C EMAIL SID
2 A@ 3
2 A@ 1
2 S@ 1
1 A@ 5

i want to delete all the more then 1 email with the same SID from the table
but the group by is not correct..
anybody help?


Cheers,

D

[Tim Van Wassenhove]

Re: sql statement group by?

On 2005-07-06, <dev> <dev> wrote:[color=blue]
> ID SID EMAIL DATE
>
> 1 3 A@ BLA
> 2 3 A@ BLA
> 3 1 S@ BLA
> 4 1 S@ BLA
> 5 5 A@ BLA
> 6 1 A@ BLA
> 7 1 A@ BLA
>
> BUT I WANT TO GET THIS
>
> C EMAIL SID
> 2 A@ 3
> 2 A@ 1
> 2 S@ 1
> 1 A@ 5
>[/color]

Untested:

SELECT COUNT(*) AS count, email, sid
FROM test
GROUO BY email, sid


--
Met vriendelijke groeten,
Tim Van Wassenhove <http://timvw.madoka.be >

[Guest]

Re: sql statement group by?


<dev> wrote in message
news:42cba697$0 $14626$e4fe514c @dreader13.news .xs4all.nl...[color=blue]
> hey all,
>
> I have a group by problem in mysql made in php code
>
> if I have this table
>
> table TEST
>
> colunms:
>
> ID
> SID
> email
> DATE
>
> and this data
>
> ID SID EMAIL DATE
>
> 1 3 A@ BLA
> 2 3 A@ BLA
> 3 1 S@ BLA
> 4 1 S@ BLA
> 5 5 A@ BLA
> 6 1 A@ BLA
> 7 1 A@ BLA
>
> and i do this query
>
>
> SELECT count(*) AS C, EMAIL, SID FROM TEST
> WHERE SID = SID GROUP BY email ORDER BY C DESC
>
> i get
>
> C EMAIL SID
> 2 A@ 3
> 2 S@ 1
> 1 A@ 5
>
>
>
> BUT I WANT TO GET THIS
>
> C EMAIL SID
> 2 A@ 3
> 2 A@ 1
> 2 S@ 1
> 1 A@ 5
>
> i want to delete all the more then 1 email with the same SID from the
> table but the group by is not correct..
> anybody help?
>
>
> Cheers,
>
> D
>
>
>[/color]

??