Posted by Erland Sommarskog on 12/19/06 22:59

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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]