Skip to content
Advertisement

How to replace Over clause in sql version 4.9.5

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 
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement