|
|
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!
Navigation:
[Reply to this message]
|