SQLTeam.com | Weblogs | Forums

Want SQL query to find the counts

sql2008r2

#1

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


#2

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.


#3

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

#4

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


#5

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

#6

Thank you so much, the query worked.


#7

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.


#8

Try

ORDER BY YEAR(yourdate), MONTH(yourdate)