Posted by isdeveloper on 12/18/06 16:13

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!

[Back to original message]