CODEWITHSUNDEEP
mysqlsql
ExtremeSwat
ExtremeSwat

Reputation: 824

display certain values mySQL

Hey guys I have a dilemma with one of my SELECTS that I use in mySQL DB. Firstly this is how it looks : This

My select is supposed to extract all the users and count each their prezente , when I use this select instead of taking all my users I get this :

SELECT users1.id,users1.Nume, COUNT(pontaj.prezente)
FROM users1, pontaj
WHERE users1.id = pontaj.id

enter image description here

Upvotes: 0

Views: 61

Answers (4)

Rick James
Rick James

Reputation: 142298

pontaj.prezente is an INT; perhaps you want SUM(pontaj.prezente) ??

Upvotes: 0

CiucaS
CiucaS

Reputation: 2128

SELECT  DISTINCT users1.id, users1.Nume, COUNT(pontaj.prezente) over (partition by users1.id)
FROM users1
INNER JOIN pontaj ON users1.id = pontaj.id

This is an alternative if you don't want to use the GROUP BY

Upvotes: 1

MenzZana
MenzZana

Reputation: 178

I think you should add a group by clause meaning at the end of the SQL add

group by users1.Nume

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

You need to add a GROUP BY clause to your query. Also replace the old join syntax using WHERE clause with recommended JOIN / ON syntax.

SELECT users1.id,users1.Nume, COUNT(pontaj.prezente)
    FROM users1
    INNER JOIN  pontaj
    ON users1.id = pontaj.id 
 GROUP BY users1.id,users1.Nume

Upvotes: 2

Related Questions