CODEWITHSUNDEEP
mysqlsql
user123
user123

Reputation: 5407

Getting count of repeated record occurrence in table

In one of my table I store user details when ever he login into it.

I want to get repeated occurrence count of each user. Means user x,y,z has logged in how many times.

SELECT count( id )
FROM user_stat
GROUP BY id

this just gives count value.

1     id        varchar(64)     latin1_swedish_ci         Yes     NULL         Change Change     Drop Drop     More Show more actions
2     login     varchar(64)     latin1_swedish_ci         Yes     NULL         Change Change     Drop Drop     More Show more actions
3     city      varchar(128)    latin1_swedish_ci         Yes     NULL         Change Change     Drop Drop     More Show more actions
4     ip        varchar(128)    latin1_swedish_ci         Yes     NULL         Change Change     Drop Drop     More Show more actions
5     logout    varchar(64)     latin1_swedish_ci         Yes     NULL         Change Change     Drop Drop     More Show more actions
6     diff      varchar(64)     latin1_swedish_ci         Yes     NULL         Change Change     Drop Drop     More Show more actions
7     username  varchar(64)     latin1_swedish_ci         Yes     NULL         Change Change     Drop Drop     More Show more actions

I want to count repeated occurrence details of each id. Means how many time person with id x has loged in.

is this possible?

Upvotes: 0

Views: 90

Answers (3)

Jason Heo
Jason Heo

Reputation: 10236

I'm not sure What "details of each id" means. If "Means how many time person with id x" then following query is what you want.

SELECT id, count(id)
FROM user_stat
GROUP BY id

BTW, your table design is not NORMALIZED. It should be splitted two table. one for login information, one for user info, so below would be better.

user_tbl(user_id, user_name);
user_stat(user_id, login, city, ip, logout, diff)

Then your query can converted like this:

SELECT user_tbl.user_id, user_tbl.user_name,  COUNT(*)
FROM user_tbl JOIN user_stat USING(user_id)
GROUP BY user_tbl.user_id, user_tbl.user_name

Upvotes: 0

Yagnesh Agola
Yagnesh Agola

Reputation: 4639

If you want user info with count that how many time user logged in try below qry.
Try this :- 

SELECT count( id ),username,id 
FROM user_stat
GROUP BY id

You will may add more column if you want.

Upvotes: 1

Sadikhasan
Sadikhasan

Reputation: 18600

select count(login_id),login_id from table_name group by login_id;

Here login_id means users_login_id. try it

Upvotes: 1

Related Questions