x
Solved

# Using count with an expression

Posted on 2010-09-20
Medium Priority
426 Views
hi,

I have a table with various columns in it and I want to produce a summary of whats in the table.

So say I have a Transactions table like

``````Customer,                    --name of the customer
Bank Name,                 --name of the bank the transaction took place
Balance,                      --current balance at the time of the transaction
Deposited,                   -- amount deposited
Withdrawn,                  -- amount withdrawn
Declined                       -- amount declined
``````

and I want query that will count records based on criteria. So I would like to group  by each customer,

``````the Bank,
Count of withdrawals
Count of Deposits
Count of where the withdrawn = balance,             -- gives me the number of trans where the customer withdrew everything
Count of where the Deposited = balance,               -- gives me the number of trans where the customer doubled their balance
Count of where Declined > 0
Percentage of monies withdrawn in terms of balance   -- % of monies withdrawn in terms of balance
``````

So my output would look something like this..

## Customer, Bank, Withdrawals, Deposits, Total withdrawal, Balance Doubled, Declined Trans, % Withdrawn

ok.. I hope its not to complex and you get the picture.

So I want to do this using nicely structured SQL and best practice and the only way I can see is that I have a heap of select sub queries... I tried to use count with an expression in a group by but cant get it to work...

regards M
0
Question by:MickyMc
• 5
• 4

LVL 21

Accepted Solution

Dale Burrell earned 2000 total points
ID: 33714566
Instead of using count, use sum(case) e.g.

select
count(*) -- Number of transactions
, sum(case when Declined > 0 then 1 else 0 end) -- Number declined
-- and so on
from Transaction
0

Author Comment

ID: 33714610
thanks Dale... is case the only expression you can use in the count or is there others... this looks good so I will try it...

How would the percentage work in the same statement using the group by...  thanks M
0

LVL 5

Expert Comment

ID: 33714619
try this
select customer,bank,Countwithdrwals=(case when Withdrawn>0 then 1 else 0 end),
countDeposited=(case when Deposited>0 then 1 else 0 end),Totalwithdrawal=sum(Withdrawn),
BalanceDoubled=sum(Deposited)-sum(Withdrawn),DeclinedTrans=(case when Declined>0 then 1 else 0 end)
from table1
0

LVL 21

Expert Comment

ID: 33714642
Percentage would be something like

sum(case when IsWithdrawl = 1 then Amount else 0 end) / sum(Amount) *100 -- Or similar
0

LVL 21

Expert Comment

ID: 33714653
Case allows you to conditionally count or sum a column, which avoids the need to lots of sub-queries. Unsure whether its the *only* way... this way certainly does what it does well so I haven't looked for alternatives. If you have other requirements that you haven't mentioned then maybe there are other solutions.
0

Author Comment

ID: 33714972
Hi Dave,  I tried this and have an issue.
I selected and grouped all records by bank... no problem with the number or records (transactions) per bank.
I then tried to use the case - count to get the number of transactions that had a full withdrawal from the account.. ie where the Withdrawn = balance. This keeps coming out with the same number as the number of transactions per bank.

Heres is my SQL

select Bank,COUNT(*), COUNT(case when Balance = Withdrawn then 1 else 0 end) As TotalWithdrawn
from dbo.BankTrans
group by Bank
Here is the database table

CREATE TABLE [dbo].[BankTrans](
[TransactionID] [bigint] IDENTITY(1,1) NOT NULL,
[Bank] [varchar](50) NULL,
[Balance] [decimal](18, 0) NULL,
[Withdrawn] [decimal](18, 0) NULL,
[Deposited] [decimal](18, 0) NULL,
[Declined] [decimal](18, 0) NULL,
[Customer] [varchar](50) NULL
) ON [PRIMARY]
Here is some inserts..

VALUES ('AIB',100,50,50,0,0,Frank;
VALUES ('AIB',200,200,200,0,0,Frank;
VALUES ('AIB',300,0,0,200,0,Mick;
VALUES ('AIB',200,0,0,200,0,Mick;
VALUES ('BOI',400,200,200,0,0,Mick;
VALUES ('BOI',200,100,100,50,0,Mick;
VALUES ('BOI',100,100,100,100,0,Mick;
VALUES ('AIB',500,0,0,200,0,Frank;
VALUES ('AIB',400,400,400,300,0,Frank;
VALUES ('BOI',400,100,100,200,0,Frank;
thanks
0

LVL 21

Expert Comment

ID: 33715012
Check my example, I'm SUMing the case, NOT counting it :)
0

Author Comment

ID: 33715051
I seen that Dale alright and thought picked me up wrong in that you thought I wanted to sum instead of count... so sorry about that.

So what you are saying Dale is that you are using the sum like count except that you add 1 to the 'sum' when the condition is matched...

would this be correct. If so... why does the count not work...

regds M
0

LVL 21

Expert Comment

ID: 33715058
Yip - summing 1/0 is the same as counting.

As for count, count does what it says on the box http://msdn.microsoft.com/en-us/library/ms175997.aspx - which isn't what you want.
0

Author Comment

ID: 33715208
cool Dale... I see now... count, actual counts the number of items in a group but we want conditional counting hence the sum.. top

points awarded and thanks again... -M
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.