Want SQL query to find the counts

I have a table with the following columns

  1. Member_number
  2. step count
  3. social_activity (which contains both Challenges and Posts)
  4. 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)

Can some one help me with this

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
1 Like

Hi Scott,
Your query worked, thank you.

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

Something roughly like this:

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
1 Like

Thank you so much, the query worked.

I have queried 2 tables to get few columns with date column.
Can some one help me with the query to sort the records by Year and Month.

Try

ORDER BY YEAR(yourdate), MONTH(yourdate)