Find No. Of Users Participating In Each Activity

[sowna]

Hi i have table that shows users participating in activities, like below fields,

 

activity1, activity2, activity3, activity4, activity5

 

and if user participating in any one activity then the value will be "Yes" else it will be "no".

 

Now i need to find the no. of users participating in 0 activities (users not particpating any activties), 1 activities, 2 activities, etc...

 

and also i need to get the number of users participating in each activities.

[Christian F.]

You really should normalize your database, and move the activities to a table of their own. That way it'll be a lot easier to count users and activities.

This sounds like it needs to be a many-to-many relationship, in which case you'll need one table for the users, one for the activities, and one to tie them together. If you do a search on the 'net for "mysql many to many relations" you should find lots of guides on how to do this.

[sowna]

Yeah i understand but i am using Form tools open source to create forms and its fields and all fields comes in single table...i was thinking to get reports but no idea triggering so far thats why i posted here to get some idea...

 

now i can't go with another table...will see to get the reports...thanks for your reply...

[Barand]

If you are using software that can't do job, ditch it.

 

First, create a subquery to simulate the normalised table that you can't be bothered to create

 

   SELECT user, 'Activity 1' as activity, 1 as participate
    FROM activities
    WHERE activity1 = 'Yes'
   UNION
   SELECT user, 'Activity 2' as activity, 1 as participate
    FROM activities
    WHERE activity2 = 'Yes'
   UNION
   SELECT user, 'Activity 3' as activity, 1 as participate
    FROM activities
    WHERE activity3 = 'Yes'
   UNION
   SELECT user, 'Activity 4' as activity, 1 as participate
    FROM activities
    WHERE activity4 = 'Yes'
   UNION
   SELECT user, 'Activity 5' as activity, 1 as participate
    FROM activities
    WHERE activity5 = 'Yes'

 

Use this subquery in both your queries.

 

1. Number of users participating in each activity

 

SELECT activity, COUNT(*) as total
FROM
   (
   SELECT user, 'Activity 1' as activity, 1 as participate
    FROM users
    WHERE activity1 = 'Yes'
   UNION
   SELECT user, 'Activity 2' as activity, 1 as participate
    FROM users
    WHERE activity2 = 'Yes'
   UNION
   SELECT user, 'Activity 3' as activity, 1 as participate
    FROM users
    WHERE activity3 = 'Yes'
   UNION
   SELECT user, 'Activity 4' as activity, 1 as participate
    FROM users
    WHERE activity4 = 'Yes'
   UNION
   SELECT user, 'Activity 5' as activity, 1 as participate
    FROM users
    WHERE activity5 = 'Yes'
   ) as acts
GROUP BY activity

 

2. Users participating in no activities

 

SELECT users.user
FROM users
   LEFT JOIN
   (
   SELECT user, 'Activity 1' as activity, 1 as participate
    FROM users
    WHERE activity1 = 'Yes'
   UNION
   SELECT user, 'Activity 2' as activity, 1 as participate
    FROM users
    WHERE activity2 = 'Yes'
   UNION
   SELECT user, 'Activity 3' as activity, 1 as participate
    FROM users
    WHERE activity3 = 'Yes'
   UNION
   SELECT user, 'Activity 4' as activity, 1 as participate
    FROM users
    WHERE activity4 = 'Yes'
   UNION
   SELECT user, 'Activity 5' as activity, 1 as participate
    FROM users
    WHERE activity5 = 'Yes'
   ) as acts
   USING (user)
WHERE acts.user IS NULL

[sowna]

Thanks sen...i will try and see if i can get the report....

[Christian F.]

I agree with Barand that you should scrap the software, since it obviously cannot handle the job you're trying to do.

Not changing would be akin to showing up on a scooter, when asked to help someone move to a new house, and refusing to go home to get your box car because you already have your scooter there.