I got a table like this
table userReport
| id | name | surname | userId | resulId |
|---|---|---|---|---|
| 1 | test | test | 123 | 1 |
| 2 | test | test | 123 | 1 |
| 3 | test | test | 123 | 2 |
| 4 | test | test | 123 | 3 |
| 5 | john | test | 124 | 3 |
| 6 | john | test | 124 | 2 |
| 7 | john | test | 124 | 1 |
| 8 | james | test | 125 | 3 |
| 9 | james | test | 125 | 2 |
My sql is trying to get name sunrame and count how many and also needed with resultId=1
SELECT
name ' ' surname
,count(userId) AS userCount
FROM [userReport]
GROUP BY userId
it gives error
Column 'userReport.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'userReport.surname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
also I want to add a where for resultId = 1 in the same select
So its be like joining the theese three selections in one
select name ' ' surname from [userReport]
select count(userId) as userCount from [userReport] group by userId
select count(userId) as resultCount from [userReport] where resulId='1' group by userId
CodePudding user response：
The quickest fix might be to just add the name and surname to the GROUP BY clause:
SELECT userId, name ' ' surname AS full_name, COUNT(*) AS userCount
FROM userReport
GROUP BY userId, name ' ' surname;
Note that I have also added userId to the SELECT clause. You might want to do this in the event that two users happen to have the same first and last name.