How to extract both type of users count

Question

I have a action table. It contain a user type admin or consumer, some actions like login, change password etc..

userID    actiontype       usertype
1         login            admin
2         login            consumer
3         Change Password   admin
4         Change password   admin
5         Change Password   consumer
...................
........

Now i have to create a result table in which i have all the count of actions by their both type of user as following table. Result table

actiontype      admin       customer
login            1          1
change Password  2          1

How i can do this.?? sorry for this, actually i need this .that was because of some misunderstanding.

score 1 · Accepted Answer · answered May 18 '15 at 18:47

1

First you need to create a result table using a SQL statement like below :

Create Table :

CREATE TABLE ResultsTable
(
ActionType varchar(50),
Count int,
UserType varchar(50)
);

Then insert data using the following query.

Insert Data :

INSERT INTO ResultsTable (ActionType, Count, UserType)
SELECT ActionType, UserType, COUNT(*) as Count
    FROM ActionTable
    GROUP BY ActionType, UserType;

answered May 18 '15 at 18:47

vinay

950
1
11
27

Please take a look at this http://stackoverflow.com/questions/1241178/mysql-rows-to-columns Its a similar question. – vinay May 18 '15 at 20:52

score 0 · Answer 2 · answered May 18 '15 at 18:38

0

Assuming that the difference between Change Password and change password is just a typo and not actually in your database, you can solve that problem with a GROUP BY clause:

SELECT actiontype, usertype, COUNT(*) as count
    FROM your_table
    GROUP BY actiontype, usertype

Look at this doc to find more details on the clause.

answered May 18 '15 at 18:38

TimoStaudinger

41,396
16
88
94

thanks for the response, ya that was a typo... i edit the question , can u plz tell me who i can solve this now. – sonal agrawal May 18 '15 at 19:09
You can solve your new requirements with an SQL query as well, however this will result in a quite a bit more complicated statement. I would personally put together the required table in your calling application (e.g. PHP). Would that be an option for you? – TimoStaudinger May 18 '15 at 19:27

How to extract both type of users count

2 Answers2