Posted by Scripty on 12/19/06 09:44

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.)

[Back to original message]