Solved

Using count with an expression

Posted on 2010-09-20
10
413 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 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