I need a halp with a query.
I have a table like this:

ID FLD1 FLD2
1 A 125
2 B 165
3 C 165
4 A 125
5 A 100
6 C 165

I need to select all the record grouped by FLD1 + FLD2 and the count of
every combination.
In this case:
A 125 2
B 165 1
C 165 2
A 100 1

I tried with
SELECT COUNT(FLD1) as c,FLD1,FLD2 from TABLE1 GROUP BY FLD1,FLD2

but it doesn't work.

Thank you in advance

bye,
max

"Massimo" <TOGLIMImassimo@pafin.it> schreef in bericht
news:Puoqi.6548$pd1.5564@tornado.fastwebnet.it...
>I need a halp with a query.
> I have a table like this:
>
> ID FLD1 FLD2
> 1 A 125
> 2 B 165
> 3 C 165
> 4 A 125
> 5 A 100
> 6 C 165
>
> I need to select all the record grouped by FLD1 + FLD2 and the count of
> every combination.
> In this case:
> A 125 2
> B 165 1
> C 165 2
> A 100 1
>
> I tried with
> SELECT COUNT(FLD1) as c,FLD1,FLD2 from TABLE1 GROUP BY FLD1,FLD2
>
> but it doesn't work.
>
> Thank you in advance
>
> bye,
> max
>
>
>

to me this seems to work,
why do you think this does not work?

"Luuk" <luuk@invalid.lan> schreef in bericht
news:46aa3dd0$0$240$e4fe514c@news.xs4all.nl...
>
> "Massimo" <TOGLIMImassimo@pafin.it> schreef in bericht
> news:Puoqi.6548$pd1.5564@tornado.fastwebnet.it...
>>I need a halp with a query.
>> I have a table like this:
>>
>> ID FLD1 FLD2
>> 1 A 125
>> 2 B 165
>> 3 C 165
>> 4 A 125
>> 5 A 100
>> 6 C 165
>>
>> I need to select all the record grouped by FLD1 + FLD2 and the count of
>> every combination.
>> In this case:
>> A 125 2
>> B 165 1
>> C 165 2
>> A 100 1
>>
>> I tried with
>> SELECT COUNT(FLD1) as c,FLD1,FLD2 from TABLE1 GROUP BY FLD1,FLD2
>>
>> but it doesn't work.
>>
>> Thank you in advance
>>
>> bye,
>> max
>>
>>
>>
>
> to me this seems to work,
> why do you think this does not work?
>
>
>

sorry, forgot this to add to my post:

mysql> select * from t27;
+------+------+
| FLD1 | FLD2 |
+------+------+
| A | 125 |
| B | 165 |
| C | 165 |
| A | 125 |
| A | 100 |
| C | 165 |
+------+------+
6 rows in set (0.00 sec)

mysql> select count(FLD1) as c, FLD1, FLD2 from t27 group by FLD1, FLD2;
+---+------+------+
| c | FLD1 | FLD2 |
+---+------+------+
| 1 | A | 100 |
| 2 | A | 125 |
| 1 | B | 165 |
| 2 | C | 165 |
+---+------+------+
4 rows in set (0.00 sec)

mysql>

Massimo wrote:
> I need a halp with a query.
> I have a table like this:
>
> ID FLD1 FLD2
> 1 A 125
> 2 B 165
> 3 C 165
> 4 A 125
> 5 A 100
> 6 C 165
>
> I need to select all the record grouped by FLD1 + FLD2 and the count
> of every combination.
> In this case:
> A 125 2
> B 165 1
> C 165 2
> A 100 1
>
> I tried with
> SELECT COUNT(FLD1) as c,FLD1,FLD2 from TABLE1 GROUP BY FLD1,FLD2
>
> but it doesn't work.
>
> Thank you in advance
>
> bye,
> max

"it doesn't work"
Well that sure tells us everything we need to know to help you.
Based on what you've said it could be:

There is no table called TABLE1
Mysql is not running
You wrote the query in Notepad and forgot to submit it to mysql
There are no records in the table
The field names are wrong

or anything else.

So how about thinking before you write and tell us in what way it doesn't
work!

> mysql> select count(FLD1) as c, FLD1, FLD2 from t27 group by FLD1, FLD2;

sorry, now it works, I'don't know why before not worked

bye,
max

> > mysql> select count(FLD1) as c, FLD1, FLD2 from t27 group by FLD1, FLD2;
>
> sorry, now it works, I'don't know why before not worked
>
> bye,
> max
>
>
i need also to select only rows whre c>1:
select count(FLD1) as c, FLD1, FLD2 from t27 where c>1 group by FLD1, FLD2;

but return me an error:
Unknown column 'c' in 'where clause'

bye,
max

On 31 Jul, 08:49, "Massimo" <TOGLIMImass...@pafin.it> wrote:
> > > mysql> select count(FLD1) as c, FLD1, FLD2 from t27 group by FLD1, FLD2;
>
> > sorry, now it works, I'don't know why before not worked
>
> > bye,
> > max
>
> i need also to select only rows whre c>1:
> select count(FLD1) as c, FLD1, FLD2 from t27 where c>1 group by FLD1, FLD2;
>
> but return me an error:
> Unknown column 'c' in 'where clause'
>
> bye,
> max

That is because 'c' is an alias.

Try:
select
count(FLD1) as c,
FLD1,
FLD2
from t27
group by FLD1, FLD2
having c>1

> Try:
> select
> count(FLD1) as c,
> FLD1,
> FLD2
> from t27
> group by FLD1, FLD2
> having c>1
>

ok, works!

thanx

bye,
max