CODEWITHSUNDEEP
mysqlsqlviewcount
Atheel
Atheel

Reputation: 177

How to use SELECT inside COUNT in MYSQL

I have this view:

myVIEW: (id,percent)

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

LASTVIEW: (lastID, lastPR, counter)

and in "counter", in every 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;

Upvotes: 6

Views: 18672

Answers (2)

Pu297
Pu297

Reputation: 151

Use the following select query for creating your view

SELECT lastID,lastPR,counter
FROM (SELECT A.id AS lastID,A.percent AS lastPR,count(B.id) AS counter
FROM myVIEW A,myVIEW B WHERE B.percent<A.percent
GROUP BY A.id,A.percent)

Upvotes: 0

Tim3880
Tim3880

Reputation: 2583

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

Upvotes: 9

Related Questions