mysql query problem...

#11

On 2011-12-10 00:00, Tony Mountifield wrote:
[...]

Quote:

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..?

select count(*)
from (
SELECT loyalty_id FROM transactions
GROUP BY loyalty_id
HAVING COUNT(*)>1
) as t

/Lennart

#12

On Sat, 10 Dec 2011 02:47:34 -0800 (PST), amvis wrote:

Quote:

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

I'm a wrapper, baby, so why don't you rap with me?

create table `test`.`TableName1`(
`id` int NOT NULL AUTO_INCREMENT ,
`loyalty_id` int NOT NULL ,
PRIMARY KEY (`id`)
);
INSERT INTO `test`.`TableName1`(`id`,`loyalty_id`) VALUES ( NULL,'1');
insert into `test`.`TableNAME1`(`ID`,`loyalty_id`) values ( NULL,'2');
insert into `Test`.`TableName1`(`id`,`loyalty_id`) vALUES ( NULL,'3');
insert into `test`.`TableName1`(`ID`,`loYALTy_id`) values ( NULL,'4');
insert INTO `TEST`.`TableName1`(`id`,`loyalty_id`) values ( NULL,'5');
insert into `test`.`TableName1`(`ID`,`lOYALty_id`) values ( NULL,'5');
insert into `TEST`.`TABLeName1`(`id`,`loyalty_id`) values ( NULL,'5');
insert into `test`.`TableName1`(`id`,`LOYALty_ID`) values ( NULL,'1');
insert into `TEST`.`TABleName1`(`id`,`loyalty_id`) values ( NULL,'3');
INSErt into `test`.`TableName1`(`id`,`LOYALTy_ID`) values ( NULL,'1');

Quote:

SELECT * FROM TableName1;
+----+------------+
id | loyalty_id |
+----+------------+
1 | 1 |
2 | 2 |
3 | 3 |
4 | 4 |
5 | 5 |
6 | 5 |
7 | 5 |
8 | 1 |
9 | 3 |
10 | 1 |
+----+------------+

10 rows in set (0.00 sec)

mysql> SELECT COUNT(*) AS cnt FROM TableName1 GROUP BY loyalty_id HAVING
cnt > 1;
+-----+

Quote:

cnt |
+-----+
3 |
2 |
3 |
+-----+

3 rows in set (0.00 sec)

Check out the hook while the DJ resolves it.

mysql> SELECT COUNT(*) AS cnt_loyaltyid FROM (SELECT COUNT(*) AS cnt
FROM TableName1 GROUP BY loyalty_id HAVING cnt > 1) AS derived_table;
+---------------+

Quote:

cnt_loyaltyid |
+---------------+
3 |
+---------------+

1 row in set (0.00 sec)

Booyah! Stop! Prettify!

SELECT COUNT(*) AS cnt_loyaltyid
FROM (
SELECT COUNT(*) AS cnt
FROM TableName1
GROUP BY loyalty_id
HAVING cnt > 1
) AS derived_table;

Can't touch this.

--
37. If my trusted lieutenant tells me my Legions of Terror are losing a
battle, I will believe him. After all, he's my trusted lieutenant.
--Peter Anspach's list of things to do as an Evil Overlord

#13

Thanks all..

Vishnu
Thank you

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

mysql query problem...

comp.databases.mysql comp.databases.mysql

Re: mysql query problem... - 12-10-2011 , 05:57 AM

Re: mysql query problem... - 12-10-2011 , 07:59 PM

Re: mysql query problem... - 12-11-2011 , 08:03 AM