Solved

Using count with an expression

Posted on 2010-09-20
10
404 Views
Last Modified: 2012-05-10
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 

Open in new window


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

Open in new window


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...

can anyone advise please...

regards M
0
Comment
Question by:MickyMc
  • 5
  • 4
10 Comments
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 500 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

by:MickyMc
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

by:Priya Perumpilavil
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

by:Dale Burrell
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

by:Dale Burrell
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:MickyMc
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..

INSERT INTO [TempScratchPad].[dbo].[BankTrans] ([Bank],[Balance],[Withdrawn],[Deposited],[Declined],[Customer])
VALUES ('AIB',100,50,50,0,0,Frank;
INSERT INTO [TempScratchPad].[dbo].[BankTrans] ([Bank],[Balance],[Withdrawn],[Deposited],[Declined],[Customer])
VALUES ('AIB',200,200,200,0,0,Frank;
INSERT INTO [TempScratchPad].[dbo].[BankTrans] ([Bank],[Balance],[Withdrawn],[Deposited],[Declined],[Customer])
VALUES ('AIB',300,0,0,200,0,Mick;
INSERT INTO [TempScratchPad].[dbo].[BankTrans] ([Bank],[Balance],[Withdrawn],[Deposited],[Declined],[Customer])
VALUES ('AIB',200,0,0,200,0,Mick;
INSERT INTO [TempScratchPad].[dbo].[BankTrans] ([Bank],[Balance],[Withdrawn],[Deposited],[Declined],[Customer])
VALUES ('BOI',400,200,200,0,0,Mick;
INSERT INTO [TempScratchPad].[dbo].[BankTrans] ([Bank],[Balance],[Withdrawn],[Deposited],[Declined],[Customer])
VALUES ('BOI',200,100,100,50,0,Mick;
INSERT INTO [TempScratchPad].[dbo].[BankTrans] ([Bank],[Balance],[Withdrawn],[Deposited],[Declined],[Customer])
VALUES ('BOI',100,100,100,100,0,Mick;
INSERT INTO [TempScratchPad].[dbo].[BankTrans] ([Bank],[Balance],[Withdrawn],[Deposited],[Declined],[Customer])
VALUES ('AIB',500,0,0,200,0,Frank;
INSERT INTO [TempScratchPad].[dbo].[BankTrans] ([Bank],[Balance],[Withdrawn],[Deposited],[Declined],[Customer])
VALUES ('AIB',400,400,400,300,0,Frank;
INSERT INTO [TempScratchPad].[dbo].[BankTrans] ([Bank],[Balance],[Withdrawn],[Deposited],[Declined],[Customer])
VALUES ('BOI',400,100,100,200,0,Frank;
thanks
0
 
LVL 21

Expert Comment

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

Author Comment

by:MickyMc
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

by:Dale Burrell
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

by:MickyMc
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now