MickyMc
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
and I want query that will count records based on criteria. So I would like to group by each customer,
So my output would look something like this..
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try this
select customer,bank,Countwithdrw als=(case when Withdrawn>0 then 1 else 0 end),
countDeposited=(case when Deposited>0 then 1 else 0 end),Totalwithdrawal=sum(W ithdrawn),
BalanceDoubled=sum(Deposit ed)-sum(Wi thdrawn),D eclinedTra ns=(case when Declined>0 then 1 else 0 end)
from table1
select customer,bank,Countwithdrw
countDeposited=(case when Deposited>0 then 1 else 0 end),Totalwithdrawal=sum(W
BalanceDoubled=sum(Deposit
from table1
Percentage would be something like
sum(case when IsWithdrawl = 1 then Amount else 0 end) / sum(Amount) *100 -- Or similar
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.
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].[Ba nkTrans] ([Bank],[Balance],[Withdra wn],[Depos ited],[Dec lined],[Cu stomer])
VALUES ('AIB',100,50,50,0,0,Frank ;
INSERT INTO [TempScratchPad].[dbo].[Ba nkTrans] ([Bank],[Balance],[Withdra wn],[Depos ited],[Dec lined],[Cu stomer])
VALUES ('AIB',200,200,200,0,0,Fra nk;
INSERT INTO [TempScratchPad].[dbo].[Ba nkTrans] ([Bank],[Balance],[Withdra wn],[Depos ited],[Dec lined],[Cu stomer])
VALUES ('AIB',300,0,0,200,0,Mick;
INSERT INTO [TempScratchPad].[dbo].[Ba nkTrans] ([Bank],[Balance],[Withdra wn],[Depos ited],[Dec lined],[Cu stomer])
VALUES ('AIB',200,0,0,200,0,Mick;
INSERT INTO [TempScratchPad].[dbo].[Ba nkTrans] ([Bank],[Balance],[Withdra wn],[Depos ited],[Dec lined],[Cu stomer])
VALUES ('BOI',400,200,200,0,0,Mic k;
INSERT INTO [TempScratchPad].[dbo].[Ba nkTrans] ([Bank],[Balance],[Withdra wn],[Depos ited],[Dec lined],[Cu stomer])
VALUES ('BOI',200,100,100,50,0,Mi ck;
INSERT INTO [TempScratchPad].[dbo].[Ba nkTrans] ([Bank],[Balance],[Withdra wn],[Depos ited],[Dec lined],[Cu stomer])
VALUES ('BOI',100,100,100,100,0,M ick;
INSERT INTO [TempScratchPad].[dbo].[Ba nkTrans] ([Bank],[Balance],[Withdra wn],[Depos ited],[Dec lined],[Cu stomer])
VALUES ('AIB',500,0,0,200,0,Frank ;
INSERT INTO [TempScratchPad].[dbo].[Ba nkTrans] ([Bank],[Balance],[Withdra wn],[Depos ited],[Dec lined],[Cu stomer])
VALUES ('AIB',400,400,400,300,0,F rank;
INSERT INTO [TempScratchPad].[dbo].[Ba nkTrans] ([Bank],[Balance],[Withdra wn],[Depos ited],[Dec lined],[Cu stomer])
VALUES ('BOI',400,100,100,200,0,F rank;
thanks
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].[Ba
VALUES ('AIB',100,50,50,0,0,Frank
INSERT INTO [TempScratchPad].[dbo].[Ba
VALUES ('AIB',200,200,200,0,0,Fra
INSERT INTO [TempScratchPad].[dbo].[Ba
VALUES ('AIB',300,0,0,200,0,Mick;
INSERT INTO [TempScratchPad].[dbo].[Ba
VALUES ('AIB',200,0,0,200,0,Mick;
INSERT INTO [TempScratchPad].[dbo].[Ba
VALUES ('BOI',400,200,200,0,0,Mic
INSERT INTO [TempScratchPad].[dbo].[Ba
VALUES ('BOI',200,100,100,50,0,Mi
INSERT INTO [TempScratchPad].[dbo].[Ba
VALUES ('BOI',100,100,100,100,0,M
INSERT INTO [TempScratchPad].[dbo].[Ba
VALUES ('AIB',500,0,0,200,0,Frank
INSERT INTO [TempScratchPad].[dbo].[Ba
VALUES ('AIB',400,400,400,300,0,F
INSERT INTO [TempScratchPad].[dbo].[Ba
VALUES ('BOI',400,100,100,200,0,F
thanks
Check my example, I'm SUMing the case, NOT counting it :)
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
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.
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.
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
points awarded and thanks again... -M
ASKER
How would the percentage work in the same statement using the group by... thanks M