1

I have a table with count as a field and username as another. I've managed to get a total count of all the records for the month in question by using this query:

select count(*) 
from forms 
where Month(date) = MONTH(CURDATE()) 
    AND YEAR(CURDATE());

I've also managed to get individual users entries by this query:

select u_name, count(*) AS numb 
from forms 
where Month(date) = MONTH(CURDATE()) 
    AND YEAR(CURDATE()) 
GROUP BY U_name 
ORDER BY numb DESC;`

So...the first gives me back something like '12'...another words, 12 records entered in October (let's say). The second query gives me something like:

John 5 Mary 7

What I'm struggeling with is a query that will give me the percentage of each users entries....i.e John's is 41%. 5 out of the 12 entries.

Hope I'm clear....thanks for any help.

Barmar
  • 741,623
  • 53
  • 500
  • 612
Xyz
  • 117
  • 8
  • You will need to use subqueries to do what you want. Not the easiest thing in the world, but it'll work! – itoctopus Oct 06 '15 at 18:23
  • 2
    would this previous some-what similar solution help: http://stackoverflow.com/questions/15746749/mysql-calculate-percentage – DTH Oct 06 '15 at 18:25
  • 1
    Are you missing something in `AND YEAR(CURDATE())` -- shouldn't that be compared equal to something? – Barmar Oct 06 '15 at 18:37
  • You mentioned that you have a field named `count`. How do you want to use that in the query? – Barmar Oct 06 '15 at 18:39

1 Answers1

1

Join the query that calculates the total with the grouped query.

SELECT u_name, COUNT(*) AS numb, COUNT(*)*100/total AS pct
FROM forms
CROSS JOIN (SELECT COUNT(*) AS total
            FROM forms
            WHERE MONTH(date) = MONTH(NOW()) AND YEAR(date) = YEAR(NOW())) AS x
WHERE MONTH(date) = MONTH(NOW()) AND YEAR(date) = YEAR(NOW())
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • That did it Barmar. Thanks! I just need to figure out how to chop down the decimal points as it's giving me results like 44.444 But other than that, Thanks to all!!! – Xyz Oct 06 '15 at 19:35