social_activity (which contains both Challenges and Posts)
High_fives
Basically all the columns 2-4 are counts for members
I want to create a report where I can display member_number, step_count, Social_activity( where it is challenges), social activity(where it is Posts), High_five
I am able to write the below query
select member_number, step_count, Social_activity
from ABC
group by Member_number
But I want the query to get the separate count for Social activity(Challenges) and Social activity(posts)
What are the columns in the ABC table? Is all the required info (steps, social activity, high fives) in the ABC table, or is it in some other table(s)? When a member does something that causes the social activity count to increase by one what is the record added/updated and in which table?
If you can post the details of the tables and some sample data, this should not be hard to do.
The general method to do that is below. Adjust the "when Social_activity" to match your actual data:
select member_number, step_count, Social_activity,
sum(case when Social_activity like 'challenge%' then 1 else 0 end) as Challenges_Count,
sum(case when Social_activity like 'post%' then 1 else 0 end) as Posts_Count
from ABC
group by Member_number
As I said I have first table that has member_key, all different counts where I used your query to see if they are adding up and it worked.
I have second table which contains Member_key and member details like Name, Relation, Gender.
My question is how can I join these two tables to get both Member details and the counts in a single query because I have to write a query for a report that contains a table with member details and counts
select
m.member_key, m.Name, m.Relation, m.Gender,
abc.step_count, abc.Social_activity, abc.Challenges_Count, abc.Posts_Count
from (
select member_number, step_count, Social_activity,
sum(case when Social_activity like 'challenge%' then 1 else 0 end) as Challenges_Count,
sum(case when Social_activity like 'post%' then 1 else 0 end) as Posts_Count
from ABC
group by Member_number
) as abc_derived
inner join members m on m.member_key = abc_derived.member_num