CODEWITHSUNDEEP
t-sqlreporting-servicescountssrs-2008
user2199661
user2199661

Reputation: 23

Count 'Number of Transactions'

I'm trying to get a count of 'number of transactions' that occurred'. The data could look like this.

Cust #   Trans#  TransType  LineItem
42        5000      1         1
42        6000      1         1
42        6000      1         2
42        6000      2         1
42        6000      2         2
42        6000      2         3

There can be multiple transaction types for any given transaction number. In this example, my desired returned 'number of transactions' count is '3', as Trans# 5000 only had one different TransType and 6000 had two. If I do a distinct count of Trans# I get '2' and if I do just a count, I get '6'.

I've tried working with:

COUNT(DISTINCT CASE Trans# WHEN ???? THEN 1 ELSE null END) AS [Num of Transactions],

But I know that I'm not quite on the right track. If anyone could point me in the right direction, it'd be much appreciated.

Upvotes: 2

Views: 3013

Answers (2)

Taryn
Taryn

Reputation: 247700

You can use the following to get the count of distinct transtype for each customer and transaction:

select cust,
  trans,
  count(distinct transtype) cnt
from yourtable
group by cust, trans;

Then if you want a total of that count, you can apply sum() to the query:

select sum(cnt) Total
from
(
  select cust,
    trans,
    count(distinct transtype) cnt
  from yourtable
  group by cust, trans
) src

See SQL Fiddle with Demo of both queries.

Upvotes: 1

praveen
praveen

Reputation: 12271

Try this :-

with cte as
(
  Select Cust,Trans,row_number() over (partition by trans,TransType order by cust) rn
  from Sample
)
Select count(*) as TransCount from cte
where rn=1

SQL FIDDLE DEMO

Upvotes: 1

Related Questions