Go Premium for a chance to win a PS4. Enter to Win

x
Solved

Using count with an expression

Posted on 2010-09-20
Medium Priority
421 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.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses
Course of the Month11 days, 3 hours left to enroll