i have a table transactions. which have 2 fields id and loyalty_id. here the Id is primary key. my table like this.

ID loyalty_id
1 1
2 1
3 1
4 2
5 2
6 3
7 3
8 4
9 4
10 5

Here the loyalty_id field have repeated values.i need the repeated values count. I tried this and i got the repeated values. But i need the count.
That means. when i run this query

SELECT loyalty_id FROM transactions
GROUP BY loyalty_id
HAVING COUNT(*)>1;

From this i got
loyalty_id

1
2
3
4
But i need the count, here the count(loyalty_id) is 4. how to get that with the above query..?


Thank you
Vishnu

amvis wrote:
You never thought of adding the count to the select?


--

//Aho

can u give more clarification?. bcz i didn't get you

In article <4785156.1318.1323452676783.JavaMail.geo-discussion-forums@pruu5>,
amvis <comp.databases.mysql (AT) googlegroups (DOT) com> wrote:
SELECT loyalty_id, COUNT(*) FROM transactions
GROUP BY loyalty_id;

You can add the "HAVING COUNT(*)>1" if you are only interested in the
loyalty_ids that occur more than once. Otherwise don't.

Cheers
Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

i think u didn't get my question..

when i using this query

SELECT loyalty_id FROM authors
GROUP BY loyalty_id
HAVING COUNT(*)>1;

the output will be

loyalty_id
1
2
4
but i don't need display all this repeated loyalty_ids. just i need to display 3. which means 3 values repeating having >1.

Thank you for the reply..

i think, u didn't get my question..

SELECT loyalty_id FROM authors
GROUP BY loyalty_id
HAVING COUNT(*)>1;

when i run this above query i got the output like

loyalty_id
1
2
4
this output means, the repeated values having count >1. the output is correct. but i don't need to display like this. i need to display, how many repeated values, here 3 values(1,2,4). i need this 3. how to display that?

Thank you
vishnu

amvis wrote:
LIMIT 3

but you most likely always have 1, 2 and 3

--

//Aho

Thank you for the reply..

no not the limit, whatever the repeated values having count>1, i need to display the count of how many values repeated

the next time loyalty id

1,1,1,2,3,3,4,4,4,5,5,6

the repeated values are(1,3,4,5) so the count is 4

Vishnu
Thank you

I'm not entirely sure that I understand the question, but this will tell you how many distinct `loyalty_id`'s there are:

SELECT COUNT(DISTINCT loyalty_id) FROM transactions;

If you're needing to know how many are duplicated (the opposite of finding the number of distinct values), try this:

SELECT COUNT(1) - COUNT(DISTINCT loyalty_id) AS 'dupes' FROM transactions;

I haven't tested that, but I think it will work. I might help get you on the right track, anyway.

jason thank for the reply...

with ur first query SELECT COUNT(DISTINCT loyalty_id) FROM transactions;

i got total count of repeated values and non-repeated values.

lets take an example... plz understand my question..

my loyalty_id values(1,1,1,2,3,3,4,5,5,5). with my query i got the correct output.

THIS IS MY QUERY..

SELECT loyalty_id FROM transactions
GROUP BY loyalty_id
HAVING COUNT(*)>1;

after run this query i got repeated values.It displays like
loyalty_id
1
3
5
bcz this 1,3,5 are repeated values and 2,4 are non-repeated values, so i don't need that.

MY PROBLEM IS, HERE DISPLAYS ALL THE REPEATED VALUES(1,3,5). BUT I NEED ONLY THE COUNT. HERE THE COUNT WILL BE 3. BECAUSE 3 VALUES REPEATING.so how to do this with my query

OUTPUT WILL BE.....

loyalty_id
3