1

I have a query where I get a result set of transaction totals grouped by a date, a store and a type. Where I'm stuck is: I want to calculate the percentages as well, I would've simply done it in Excel, but due to bad naming conventions, my number of rows per store is inconsistent and my result set is pretty big.

Here is my query without the percentages:

    SELECT DATEPART(Year,datecreated) as [Year],
           DATEPART(Month,datecreated) as [Month],
           b.Name as [Store]
           Type as [Type],
           Count(Transaction) as [Total],
           SUM(Amount) as [Value],
       -- one or two other aggregate functions
   FROM MyTransactions a, MyStores b
   WHERE a.Status = 'Paid'
   AND a.ID = b.ID
   -- AND Date -- daterange
   GROUP BY  
   datepart(year,datecreated),datepart(month,datecreated),Type
   ORDER BY year desc,month desc,type desc, store desc

Which works perfectly fine, now I just need the best way to determine the percentage of transactions per type. e.g. 60% of the total and 22% of the value is of the type 'Credit' at the specified store during Month 9 of 2013.

Do you perhaps have any suggestions for me? Would be greatly appreciated.

EDIT:

The result I am looking for looks a bit like this:

2012 | 10 | Store1 | Credit | 100 | 50%
2012 | 10 | Store1 | Debit  | 100 | 50%
2012 | 10 | Store2 | Credit | 400 | 40%
2012 | 10 | Store2 | Debit  | 600 | 60%

etc. (obviously with a few other values and percentages)

Hanno Opperman
  • 143
  • 2
  • 12
  • Can you try looking at any of those, look similar: [Stack overflow percentage count in SQL Server](http://stackoverflow.com/questions/10134905/sql-percentage-count) [Yet another percentage count in SQL Server](http://stackoverflow.com/questions/770579/how-to-calculate-percentage-with-a-sql-statement/5846102#5846102) – Przemyslaw Kruglej Oct 15 '13 at 08:47
  • I did have a look at many of those (did try some of those solutions). I think my problem is more with my grouping, and getting the correct (relational) total to calculate the percentages. – Hanno Opperman Oct 15 '13 at 08:58

1 Answers1

3

Using a CTE to provide your filtered results (NB: the join syntax rather than joining in the where clause)...

;with cte as (
      select 
           datepart(yyyy,datecreated) dateyear,
           datepart(mm,datecreated) datemonth,
           b.name as store,
           type,
           [transaction] as t,
           amount 
from
     myTransactions a inner join mystores b
          on a.id = b.id
where a.status='paid'
 )
 select 
      dateyear,
      datemonth,
      [Store],
      [Type],
      Count(T) as [Total],
      SUM(Amount) as [Value],
      100.0*count(T) / 
           (Select count(T) from cte c1 where c1.store=cte.store and c1.dateyear = cte.dateyear and c1.datemonth=cte.datemonth),
      100.0*Sum(Amount) / 
           (Select sum(amount) from cte c1 where c1.store=cte.store and c1.dateyear = cte.dateyear and c1.datemonth=cte.datemonth)
 from cte
 group by
      dateyear, datemonth,Type, store

Then from those results, a simple percentage calculation

podiluska
  • 50,950
  • 7
  • 98
  • 104
  • Thanks for the answer, but won't (select count(T) from cte) and (select sum(amount) from cte) give me the same total value for all of my rows? – Hanno Opperman Oct 15 '13 at 09:23
  • The total needs to be relational to the store and date though, that's why I've been struggling with my CTE (it returns the same total for all of the rows, I need the total of the year,month,store and all of the types in order to calculate the percentage. In the CTE's case it gives me the same total across all of the rows) – Hanno Opperman Oct 15 '13 at 09:53
  • Dude! Now I feel so embarrassed, such a simple solution. Thanks a mil! – Hanno Opperman Oct 15 '13 at 10:13