Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

Using count with an expression

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
MickyMc
Asked:
MickyMc
  • 5
  • 4
1 Solution
 
Dale BurrellDirectorCommented:
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
 
MickyMcAuthor Commented:
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
 
Priya PerumpilavilSoftware EngineerCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Dale BurrellDirectorCommented:
Percentage would be something like

sum(case when IsWithdrawl = 1 then Amount else 0 end) / sum(Amount) *100 -- Or similar
0
 
Dale BurrellDirectorCommented:
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
 
MickyMcAuthor Commented:
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
 
Dale BurrellDirectorCommented:
Check my example, I'm SUMing the case, NOT counting it :)
0
 
MickyMcAuthor Commented:
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
 
Dale BurrellDirectorCommented:
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
 
MickyMcAuthor Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now