Hi All,

I have a problem with a table that I want to get nice data out of in a
single query. The guys here reckon it can't be done in a single query
but I wanted to prove them wrong !! Essentially, I want to get the same
column out of the single table, but in one case the column must have a
where clause associated with it, and the other case it does not have a
where clause...

Lets say have a table like this :-

date || user || transaction type || Amount

so, each row contains a transaction type, and a corresponding amount
for this transaction.

There can be any number of transactions (and transaction types) per
user per day.

Here is what I want :

I want to get one particular transaction type as a percentage of the
total transactions : for example, a list of the % amount that Debit
transactions have occurred for a user for a day, with respect to all
transactions that the user has done that day, so :

Debits Jim performed on day 1 are 50% of all transactions he performed
Debits Jim performed on day 2 are 55% of all transactions he performed
... and so on.

At the moment, I do this :

select date, user, sum(amount) as debit_Amount where transaction_type
='debit'
group by date, user

and dump that into tmp table Debits

then I do

select date, user, sum(amount) as total_Amount
group by date, user

and dump this into tmp table Totals

and then I have to do a :

SELECT Debits.User, (Debits.debit_Amount / Totals.total_Amount) as
perc_Debit , Debits.date
FROM Debits, Totals
WHERE Debits.date = Totals.date AND Debits.User = Totals.User

Can anyone suggest a way of doing this without the need for these
temporary tables???

Thanks!

select date,
user,
sum(case when transaction_type ='debit' then amount else 0 end)
/
sum(amount) as perc_Debit
from mytable
group by date, user

isdeveloper@hotmail.com wrote:

> date || user || transaction type || Amount

I hope this table has a primary key, and you just omitted mentioning it
because this particular case doesn't use it.

> I want to get one particular transaction type as a percentage of the
> total transactions : for example, a list of the % amount that Debit
> transactions have occurred for a user for a day, with respect to all
> transactions that the user has done that day, so :
>
> Debits Jim performed on day 1 are 50% of all transactions he performed
> Debits Jim performed on day 2 are 55% of all transactions he performed
[snip]
> Can anyone suggest a way of doing this without the need for these
> temporary tables???

Untested:

select date, user,
coalesce(
sum(case transaction_type when 'debit' then amount else 0 end)
, 0
) / sum(amount) as perc_debit
from the_table
group by date, user

markc600@hotmail.com wrote:

> select date,
> user,
> sum(case when transaction_type ='debit' then amount else 0 end)
> /
> sum(amount) as perc_Debit
> from mytable
> group by date, user

You're right, I guess coalesce() isn't needed. (It would have been
needed in the temp-tables approach, if you wanted to pick up date/user
combinations with 0% debits.)

Thanks Guys,

I will give this a go. Is this T-SQL / pl-SQL or is this a technique
used only on SQL Server ?

Yes, Ed, there's primary keys, indices, etc etc on this table. Just
wanted to post the bare bones of the problem, rather than labour you
with all the other details..

cheers,

Scripty.


Ed Murphy wrote:
> markc600@hotmail.com wrote:
>
> > select date,
> > user,
> > sum(case when transaction_type ='debit' then amount else 0 end)
> > /
> > sum(amount) as perc_Debit
> > from mytable
> > group by date, user
>
> You're right, I guess coalesce() isn't needed. (It would have been
> needed in the temp-tables approach, if you wanted to pick up date/user
> combinations with 0% debits.)

Just a quick note:

this nested "case when" works in MSSQL Server. It it better than the
temporary table approach not only as it is faster, but also because it
picks up dates where zero debits occur, as well as days when they do.
My original temporary table approach only picks out dates that match
from both initial queries.
I must read up on using 'case when....' in statements.

Thanks again

S


Scripty wrote:
> Thanks Guys,
>
> I will give this a go. Is this T-SQL / pl-SQL or is this a technique
> used only on SQL Server ?
>
> Yes, Ed, there's primary keys, indices, etc etc on this table. Just
> wanted to post the bare bones of the problem, rather than labour you
> with all the other details..
>
> cheers,
>
> Scripty.
>
>
> Ed Murphy wrote:
> > markc600@hotmail.com wrote:
> >
> > > select date,
> > > user,
> > > sum(case when transaction_type ='debit' then amount else 0 end)
> > > /
> > > sum(amount) as perc_Debit
> > > from mytable
> > > group by date, user
> >
> > You're right, I guess coalesce() isn't needed. (It would have been
> > needed in the temp-tables approach, if you wanted to pick up date/user
> > combinations with 0% debits.)

Ok, So here is the next question:

What if I want a view of every transaction type, with a corresponding
figure of the overall transaction value, per row in the query ?

just for the record, the transact table looks like this:

date|| userID || transaction_type || amount

MarkC gave me this:-
----
select date,
user,
sum(case when transaction_type ='debit' then amount else 0 end)
/
sum(amount) as perc_Debit
from mytable
group by date, user
----

Which is great for creating a table of stats for each user's 'debit'
transactions:

date || User1 || 60% Debit transactions ||

So now lets say I want this instead:

date1 || User1 || Debit || 60%
date1 || User1 || Credit || 35%
date1 || User1 || Enquiry || 5%



So we could go for something like this :
--
select date,
user,
transaction_type,
sum(
case
when transaction_type ='debit' then amount else
when transaction_type ='credit' then amount else
when transaction_type ='...' then amount else
....
0 end)
/
sum(amount) as perc_Debit
from mytable
group by date, user
---

But! that is no good because in the particular application we have
(Don't ask) we cannot be sure of all transaction types, indeed, new
ones can be added all the time. The actual table I'm dealing with is
normalised as well (I'm leaving out the exact details here because I
don't want to swamp people with schemas / ER diagrams that aren't
entirely relevant). So I really need something like:

--- DOES NOT WORK ---
select date,
user,
transaction_type,
sum(
case
when transaction_type
IN (SELECT DISTINCT transactionTypeID FROM Transaction_List) then
amount
else
0
end)
/
sum(amount) as perc_Item
from mytable
group by date, user

--- DOES NOT WORK ---

Thats not going to work is it?? Is there some sort of foreach statement
I can run here or should I be building up a table by running the first
query many times , once for each transaction type?

Any ideas on the new problem?? If not I think I'll create stored proc
that I can run on (say) the top 5 transaction_types per day per
user...

Scripty wrote:
> Just a quick note:
>
> this nested "case when" works in MSSQL Server. It it better than the
> temporary table approach not only as it is faster, but also because it
> picks up dates where zero debits occur, as well as days when they do.
> My original temporary table approach only picks out dates that match
> from both initial queries.
> I must read up on using 'case when....' in statements.
>
> Thanks again
>
> S
>
>
> Scripty wrote:
> > Thanks Guys,
> >
> > I will give this a go. Is this T-SQL / pl-SQL or is this a technique
> > used only on SQL Server ?
> >
> > Yes, Ed, there's primary keys, indices, etc etc on this table. Just
> > wanted to post the bare bones of the problem, rather than labour you
> > with all the other details..
> >
> > cheers,
> >
> > Scripty.
> >
> >
> > Ed Murphy wrote:
> > > markc600@hotmail.com wrote:
> > >
> > > > select date,
> > > > user,
> > > > sum(case when transaction_type ='debit' then amount else 0 end)
> > > > /
> > > > sum(amount) as perc_Debit
> > > > from mytable
> > > > group by date, user
> > >
> > > You're right, I guess coalesce() isn't needed. (It would have been
> > > needed in the temp-tables approach, if you wanted to pick up date/user
> > > combinations with 0% debits.)

Scripty (isdeveloper@hotmail.com) writes:
> What if I want a view of every transaction type, with a corresponding
> figure of the overall transaction value, per row in the query ?
>
> just for the record, the transact table looks like this:
>
> date|| userID || transaction_type || amount
>
> MarkC gave me this:-
> ----
> select date,
> user,
> sum(case when transaction_type ='debit' then amount else 0 end)
> /
> sum(amount) as perc_Debit
> from mytable
> group by date, user
> ----
>
> Which is great for creating a table of stats for each user's 'debit'
> transactions:
>
> date || User1 || 60% Debit transactions ||
>
> So now lets say I want this instead:
>
> date1 || User1 || Debit || 60%
> date1 || User1 || Credit || 35%
> date1 || User1 || Enquiry || 5%


I think this would work on SQL 2005:

SELECT date, user, transaction_type,
100 * SUM(amount) / SUM(amount) OVER (PARTITION BY date, user)
FROM mytable
GROUP BY date, user, transaction_type

On SQL 2000 you could maybe do:

SELECT a.date, a.user, a.transaction_type, 100 * SUM(a.amount) / b.amt
FROM mytable a
JOIN (SELECT date, user, SUM(amount)
FROM mytable
GROUP BY date, user) AS b ON a.user = b.user
AND a.date = b.date
GROUP BY a.date, a.user, a.transaction_type, b.amt

Both these solutions are untested, since you did not include CREATE
TABLE statements, INSERT statements with sample data, and the desired
result from the sample.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx