MySQL using Count in different way

Question

I've researched about this code:

select *,
  year(curdate())-year(dbirth) - (right(curdate(),5) < right(dbirth,5)) as age 
from       resident_info 
having age = 18;

but what i wanted is after getting all info that already 18 I wanted to count them. Is there any way i could enter two query at the same time or any solutiomn for my problem? Thanks a lot

You need 2 Query. One to gather all the records and one that counts it. If you just need the count on code side, you can get the size of the result set from your query. — araknoid
– araknoid, Commented Feb 25, 2013 at 16:40

Taryn · Accepted Answer · 2013-02-25 17:18:12Z

1

Unless I am missing something, if you need the count of these, then you can use:

select count(*)
from
(
  select *,
   year(curdate())-year(dbirth) - (right(curdate(),5) < right(dbirth,5)) as age 
  from       resident_info 
 having age = 18
) src

If you want to return all of the data from resident_info and the count, then you can use a CROSS JOIN:

select *, 
   year(curdate())-year(dbirth) - (right(curdate(),5) < right(dbirth,5)) as age,
   src.Total
from resident_info 
cross join
(
    select count(*) Total
    from
    (
        select year(curdate())-year(dbirth) - (right(curdate(),5) < right(dbirth,5)) as age 
        from  resident_info 
        having age = 18
    ) x
) src
having age = 18

edited Feb 25, 2013 at 17:18

answered Feb 25, 2013 at 16:37

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

4 Comments

janmark Over a year ago

can i ask you one more question i used your second code and it ended like this: SELECT * , YEAR( CURDATE( ) ) - YEAR( bday ) - ( RIGHT( CURDATE( ) , 5 ) < RIGHT( bday, 5 ) ) AS age FROM resident_information CROSS JOIN ( SELECT COUNT( * ) Total FROM ( SELECT YEAR( CURDATE( ) ) - YEAR( bday ) - ( RIGHT( CURDATE( ) , 5 ) < RIGHT( bday, 5 ) ) AS age FROM resident_information HAVING age =23 )x )src HAVING age =23 how can i use total to put it in a field ot text field?

Taryn Over a year ago

@janmark you should be able to access the value from the SELECT statement. See my edit, I added src.Total to the select list.

janmark Over a year ago

can i ask one more what if i wanted to add 1 more condition where color = 'red'; where should i put it in this code select , year(curdate())-year(dbirth) - (right(curdate(),5) < right(dbirth,5)) as age, src.Total from resident_info cross join ( select count() Total from ( select year(curdate())-year(dbirth) - (right(curdate(),5) < right(dbirth,5)) as age from resident_info having age = 18 ) x ) src having age = 18

Taryn Over a year ago

@janmark If you want to apply the color=red filter to the total count and the final product, then you would have to include the where clause in two places in the inner query and the outer query

Purus · Accepted Answer · 2013-02-25 16:38:35Z

0

This might help.

select temp.*,
   year(curdate())-year(dbirth) - (right(curdate(),5) < right(dbirth,5)) as age 
       FROM resident_info AS temp
       WHERE age = 18;

answered Feb 25, 2013 at 16:38

Purus

5,8499 gold badges55 silver badges92 bronze badges

Collectives™ on Stack Overflow

MySQL using Count in different way

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related