CODEWITHSUNDEEP
mysqlsqljdbc
The Well
The Well

Reputation: 884

value from main query is used by the subquery

SELECT 
   userid, lastname, firstname , (SELECT COUNT(day) FROM PROFILE WHERE userid = ?) as numday
FROM PROFILE

I want to get each user's number of days present in the PROFILE table but Im confused of what value should I place in the question mark (?) in the query.

Please help...

Upvotes: 0

Views: 35

Answers (2)

Saravana Kumar
Saravana Kumar

Reputation: 3729

Try this

SELECT 
   userid, lastname, firstname ,
  (SELECT COUNT(day) FROM PROFILE WHERE userid = P.userid) as numday
FROM PROFILE P

Updated: using GROUP BY

SELECT userid, lastname, firstname, COUNT(day) as numday
FROM PROFILE
GROUP BY userid, lastname, firstname

Upvotes: 1

Jens
Jens

Reputation: 69450

Try this untested query:

SELECT 
   userid, lastname, firstname , numday
FROM PROFILE as p join (SELECT COUNT(day) as numday, userid FROM DTR group by userid ) as q on p.userid = q.userid

Upvotes: 0

Related Questions