How can i do it in single SQL?-CodePudding

I have this Table like this

ID | NAME     | AGE | ADDRESS   | SALARY

and an ID (2)

AGE of ID 2 is 25,

Now i need to count total number of record with AGE 25. How can i do it in single SQL Query? is there any why?

i currently doing it in 2 query. in one i return AGE with ID.

select AGE from table_name where ID = 2

it return AGE 25

and 2nd query i count number of record with this AGE.

select COUNT(*) from table_name where age =25

i want to do it in one SQL Query. is there any way ?

CodePudding user response：

use SQL COUNT function

select COUNT(*) from mytable where age =25

CodePudding user response：

You want to count the number of rows with the same age as the specified ID?

You can use a windowed count

select Agecount
from (
    select *, Count(*) over (partition by Age) Agecount
    from YourTable
)t
where id=?

CodePudding user response：

You can use count with a subquery that gets the age of the person with the wanted id:

select count(id) from table_name where age = (select age from table_name where id = 2)

CodePudding user response：

Use Group by with subquery

SELECT COUNT(*) AS ageCount
FROM table_name
WHERE age in (SELECT age FROM table_name WHERE ID = 2)
GROUP BY age

CodePudding user response：

For Questions like this the best way is use multiple conditions using Where keyword with And-Or Operators. This query should work for your requirement without complicating anything.

select count(*) from table_name where ID=2 and age=25;