SQL percentage of unique rows by row value?

Question

I have ONE table (Transactions) with columns 'TransID','StoreName' and 'Amount'. There are several stores listed with an Amount of '0'.

TRANSACTIONS
TransID   StoreName            Amount
1         Able Store             45
2         Baker Store            23
3         Charlie Store          9
4         Baker Store            0  
5         Charlie Store          0
6         Charlie Store          0

I need to generate a query to tell me for each distinct StoreName, What is the percentage of < $0.01 to > $0.01 for each store and output it.

In the above table, the result should look like this:

Able Store       100%
Baker Store      50%
Charlie Store    33%

I have tried COUNT, GROUP BY but I can't figure out how to do it...or if it is even possible...Or maybe i am just not that sharp...

Any suggestions are very welcome. I still have a few hairs left to pull out....

What if i wanted to output the results on a monthly basis? STORE MONTH1 MONTH2 MONTH3 ... Able Store 100% 50% Baker Store 50% 0% Charlie Store 33% 75%

Answer 1

This query works fine with not negative amount values.

select storename, round(avg(sign(amount)) * 100, 2) 
  from transactions group by storename;

If there can be negative amounts and you want to find the percentage of positive, use this query:

select storename, round(avg(case when amount > 0 then 1 else 0 end) * 100, 2) 
  from transactions group by storename;

If there can be negative amounts and you want to find the percentage of positive && negative, use this query:

select storename, round(avg(abs(sign(amount))) * 100, 2) 
  from transactions group by storename;