I need to get name and count value in table

Question

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

Tim Biegeleisen · Accepted Answer · 2022-01-07 07:43:43Z

1

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.

answered Jan 7, 2022 at 7:43

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

4 Comments

masterAvatarr Over a year ago

But is this a good idea to grouping by strings join

Tim Biegeleisen Over a year ago

@masterAvatarr Performance wise it's not necessarily the best thing, but you may not have a choice.

masterAvatarr Over a year ago

Okay i changed a bit and then worked .But do i have to group each column i want to select

Tim Biegeleisen Over a year ago

Yes, in general every column which appears in the SELECT has to also appear in the GROUP BY unless that column appears inside an aggregate function such as MIN or MAX.

Collectives™ on Stack Overflow

I need to get name and count value in table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related