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

x
?
Solved

Using count with an expression

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

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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.

885 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