Atheel Atheel - 3 months ago 11
MySQL Question

How to use SELECT inside COUNT in MYSQL

I have this view:

myVIEW: (id,percent)

I want to make another view, wich will be like this:

LASTVIEW: (lastID, lastPR, counter)

and in "counter", in evrey line I want to have how money id`s have a bigger percent than the percent of this line. so I tried:

CREATE VIEW LASTVIEW(lastID, lastPR, counter) AS
SELECT id AS lastID, percent AS lastPR
COUNT (SELECT id FROM myVIEW WHERE percent < lastPR) AS counter,
FROM myVIEW;

Answer

Your are almost there. Try this:

SELECT id AS lastID, percent AS lastPR, (SELECT Count(id) 
FROM myVIEW bigger 
WHERE bigger.percent > myv.percent) AS counter
FROM myVIEW myv