Need help with SQL query in Teradata

teradata

I have a table with the following columns

member_number
step_count 
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') and high_fives.

I wrote the 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?

Best Answer

Seems like you need a conditional sum based on CASE:

select member_number,
   sum(case when social_activity = 'challenges' then step_count else 0 end) as challenges,
   sum(case when social_activity = 'posts' then step_count else 0 end) as posts
from ABC
group by member_number

Related Solutions

SQL MAX function

I believe Teradata supports window functions, so you can use them to pick the larges value.

select name, age
from (
   select name, age, 
          dense_rank() over (order by age desc) as rn
   from MyTable
) t
where rn = 1;

If there are multiple names with the same age, all would be shown. If you only want to pick one, use row_number() instead of dense_rank()

Using window functions is very often faster than sub-selects.

How to execute unix commands within nzsql

The documentation for nzsql states that to shell-out you just have to use blackslash pling, followed by the command:

\! command

If you need to know how to test for file size in a shell script, you can use something similar to:

if [[ -f $file ]] && [[ -s $file ]]; then 
  echo "File exists and is greater than 0 bytes" ; 
  rm -f $file
fi

-f checks that it exists, -s checks if it's greater than 0 bytes.

Best Answer

Related Solutions

SQL MAX function

How to execute unix commands within nzsql

Related Question