1

I've looked over similar questions and I just can't seem to get this right.

I have a table with three columns: ID, Date, and Method. None are unique.

I want to be able to see for any given date, how many rows match a certain pattern on Method.

So, for example, if the table has 100 rows, and 8 of them have the date "01-01-2020" and of those 8, two of them have a method of "A", I would want a return row that says "01-01-2020", "8", "2", and "25%".

My SQL is pretty rudimentary. I have been able to make a query to get me the count of each method by date:

select Date, count(*) from mytable WHERE Method="A" group by Date;

But I haven't been able to figure out how to put together the results that I am needing. Can someone help me out?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
teekno
  • 11
  • 2
  • 1
    See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Sep 05 '20 at 20:24
  • In order to get count for every method + date - you'll need to add the Method field to the group by and select clauses, In order to get the percentage of each method+date out of the total - you can use the following : https://stackoverflow.com/questions/770579/how-to-calculate-percentage-with-a-sql-statement select Date, Method, count(*) from mytable WHERE Method="A" group by Date, Method; – planben Sep 06 '20 at 06:21

3 Answers3

0

You could perform a count over a case expression for that method, and then divide the two counts:

SELECT   date,
         COUNT(*),
         COUNT(CASE method WHEN 'A' THEN 1 END),
         COUNT(CASE method WHEN 'A' THEN 1 END) / COUNT(*) * 100
FROM     mytable
GROUP BY date
Mureinik
  • 297,002
  • 52
  • 306
  • 350
0

I'm assuming you're interested in all methods rather than just 'A', so you could do the following:

with ptotals as
(
    SELECT   
        thedate,
        count(*) as NumRows
    FROM     
        mytable
    group by
        thedate
)

select
    mytable.thedate,
    mytable.themethod,
    count(*) as method_count,
    100 * count(*) / max(ptotals.NumRows) as Pct 
from
    mytable
inner join
    ptotals
on
    mytable.thedate = ptotals.thedate
group by
    mytable.thedate,
    mytable.themethod
Nabav
  • 251
  • 1
  • 6
0

You can use AVG() for the ratio/percentage:

SELECT date, COUNT(*),
       SUM(CASE WHEN method = 'A' THEN 1 ELSE 0 END),
       AVG(CASE WHEN method = 'A' THEN 100.0 ELSE 0 END)
FROM  t
GROUP BY date;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786