How to use SELECT inside COUNT in MYSQL

Question

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;

Use your other query as a subquery in your FROM. Read this — adarsh
– adarsh, Commented May 14, 2015 at 19:05

Tim3880 · Accepted Answer · 2015-05-14 19:22:23Z

9

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

answered May 14, 2015 at 19:22

Tim3880

2,5831 gold badge13 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Atheel Over a year ago

it works! thanks! but I have a strange Segmentation fault (core dumped)!

Atheel Over a year ago

do you know why is the Segmentation ?

Tim3880 Over a year ago

I have no ideas how a database system get segmentation fault. Are you using your own program to run the query? If so, we need check your code.

Atheel Over a year ago

ok I put it in the edit. it is not the same parameters as before because now I put the real code copy paste

Tim3880 Over a year ago

Check the size of my_cmd. You now have a.much longer query.

Pu297 · Accepted Answer · 2015-05-14 19:29:17Z

0

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)

answered May 14, 2015 at 19:29

Pu297

1518 bronze badges

Collectives™ on Stack Overflow

How to use SELECT inside COUNT in MYSQL

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related