php - Select count of all rows but output rows where age <=30

859
"id"    "name"      "age"   "address"       "pincode"
"1"     "Peter"     "25"    "1 Elm Street"  "91550"
"2"     "James"     "30"    "2 Elm Street"  "91551"
"3"     "John"      "32"    "3 Elm Street"  "91552"
"4"     "Andrew"    "33"    "4 Elm Street"  "91553"

Is it possible to select the count of all the rows here, but display only the ones where age <=30 ?

I need to display only users where age <=30 and provide a link to view other users. The link appears only if there are other users > 30.

I presently do this using 2 selects and php. I looking for a better way to do this, rather than using 2 selects.

Desidred Output

"id"    "name"      "age"   "address"       "pincode"
"1"     "Peter"     "25"    "1 Elm Street"  "91550"
"2"     "James"     "30"    "2 Elm Street"  "91551"
LINK GOES HERE -> View 2 other users //This is the diff from rows outputted and total rows
855

Answer

Solution:

You must use two queries. One to get the total count:

SELECT COUNT(*) FROM people;

And one to select the users under age 30.

SELECT * FROM people WHERE age < 30;

The only way to do this with a single query would be to use a sub-select:

SELECT (SELECT COUNT(*) FROM people) AS total_count,* FROM people WHERE age < 30;

But this is no less efficient, as you're still executing two statements (logically, you're executing many more statements, as theSELECT COUNT(*)... statement would be executed once for every row of output, but your query optimizer ought to optimize that away since they'reall identical), and then you'd have an extratotal_count column in each row, which you don't want.

709

Answer

Solution:

You mean, doing all the process that you mentioned in a sql?

I don't think it is possible. The only way will be select all rows and in a loop check if age <= 30.

769

Answer

Solution:

Here's the query:

SELECT p.*, (SELECT COUNT(id) FROM people WHERE age > 30) as link_count 
FROM people p  WHERE p.age <= 30
125

Answer

Solution:

SELECT COUNT(*) FROM `people` WHERE `age` <= 30

People are also looking for solutions to the problem: windows - Apache is not responding during expensive PHP operation

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.