Link to home
Start Free TrialLog in
Avatar of MickyMc
MickyMcFlag for Ireland

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Dale Burrell
Dale Burrell
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MickyMc

ASKER

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
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
Percentage would be something like

sum(case when IsWithdrawl = 1 then Amount else 0 end) / sum(Amount) *100 -- Or similar
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.
Avatar of MickyMc

ASKER

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
Check my example, I'm SUMing the case, NOT counting it :)
Avatar of MickyMc

ASKER

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
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.
Avatar of MickyMc

ASKER

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