I am trying to run a query to find the count and percentage of artisan based on gender. The query is running absolutely fine on the SQL server 8. But not on my live server which is 4.9.5. The below is my query.
SELECT industry , count(*) as cnt , (count(*)*100.0/sum(count(*))over()) as perc FROM `artisan_work` GROUP BY industry ORDER BY cnt DESC
Advertisement
Answer
In any database, you should be able to use:
SELECT aw.industry, count(*) as cnt, count(*) * 100.0 / tot.cnt as perc FROM artisan_work aw cross join (SELECT COUNT(*) as cnt FROM artisan_work) tot GROUP BY aw.industry ORDER BY cnt DESC