Menu
Community /Pin to ProfileBookmark
@NogDogFeb 11.2011 — #Yikes! That looks really non-normalized.
Not really sure I followed all of that, but there is an AVG() function you can use with a GROUP BY expression to handle the averaging for you, rather than doing that UNION stuff. (Also, are you sure there couldn't be a JOIN or two instead of all those IF() statements for the names?)
Combined result using PHP
Hello
How can I count in MySQL query using I think so some PHP loop % difference between all records and only confirmed records and display it by months and years.
id | column 1 | column 2 | Date
1 | confirmed| Nicky | 2011-01-01
2 | confirmed| Nicky | 2011-01-01
3 | lost | Nicky | 2011-01-01
4 | lost | Nicky | 2011-01-01
So I can get a dynamic result of:
Nicky
January 50%
as (2 * 100) / 4 = 50
so far I have got this
SELECT MONTH(CurrentDate), YEAR(CurrentDate), COUNT(*),
SUM(IF(idFranchise = ‘1’, 1,0)) AS `Nicky
SUM(IF(idFranchise = ‘2’, 1,0)) AS `Alice
SUM(IF(idFranchise = ‘3’, 1,0)) AS `Sam
SUM(IF(idFranchise = ‘4’, 1,0)) AS `Beauty
SUM(IF(idFranchise = ‘5’, 1,0)) AS `Danielle
SUM(IF(idFranchise = ‘6’, 1,0)) AS `Stephanie
SUM(IF(idFranchise = ‘7’, 1,0)) AS `Jenny
SUM(IF(idFranchise = ‘8’, 1,0)) AS `Marie
SUM(IF(idFranchise = ‘9’, 1,0)) AS `Kim
SUM(IF(idFranchise = ’10’, 1,0)) AS `Dannique
COUNT(idFranchise) AS `total
FROM appointment
WHERE YEAR(CurrentDate) = ‘2010’
GROUP BY YEAR(CurrentDate), MONTH(CurrentDate)
UNION
SELECT MONTH(CurrentDate), YEAR(CurrentDate), COUNT(*), css,
SUM(IF(idFranchise = ‘1’, 1,0)) AS `Nicky
SUM(IF(idFranchise = ‘2’, 1,0)) AS `Alice
SUM(IF(idFranchise = ‘3’, 1,0)) AS `Sam
SUM(IF(idFranchise = ‘4’, 1,0)) AS `Beauty
SUM(IF(idFranchise = ‘5’, 1,0)) AS `Danielle
SUM(IF(idFranchise = ‘6’, 1,0)) AS `Stephanie
SUM(IF(idFranchise = ‘7’, 1,0)) AS `Jenny
SUM(IF(idFranchise = ‘8’, 1,0)) AS `Marie
SUM(IF(idFranchise = ‘9’, 1,0)) AS `Kim
SUM(IF(idFranchise = ’10’, 1,0)) AS `Dannique
COUNT(idFranchise) AS `total
FROM appointment
WHERE YEAR(CurrentDate) = ‘2010’ AND css = ‘confirmed’
GROUP BY YEAR(CurrentDate), MONTH(CurrentDate)
Thanks
Sign in
to post a comment1 Comments(s) ↴
0
@NogDogFeb 11.2011 — #Not really sure I followed all of that, but there is an AVG() function you can use with a GROUP BY expression to handle the averaging for you, rather than doing that UNION stuff. (Also, are you sure there couldn't be a JOIN or two instead of all those IF() statements for the names?)