Number of lessons for each user

Transact-SQL
1

I have a table that lists results of users success.
each user have a few results for each lesson.
the table fields are:
userName,result,lessonNum
I would like to know how many lessons each student had.
to know the number of users I write
SELECT distinct(username)
FROM table1
I want an additional column that will hold the number of lessons beside each student
I tried
SELECT distinct(username),count(distinct(lessonNUm))
FROM table1
but it is not working,

2

This:

SELECT username, COUNT(lessonNum) AS lessonCount
FROM table1
GROUP BY username

or this:

SELECT username, COUNT(DISTINCT lessonNum) AS lessonCount
FROM table1
GROUP BY username
2 Likes
3 
SELECT username, lessonNUm, count(*) [Count]
FROM table1
GROUP BY username, lessonNUm;
4

that is what I was looking for (one line for each userName and beside it the total number of lessons he got)
SELECT username, COUNT(DISTINCT lessonNum) AS lessonCount
FROM table1
GROUP BY username

this one
SELECT username, COUNT(lessonNum) AS lessonCount
FROM table1
GROUP BY username
gives strange results

and this one
SELECT username, lessonNUm, count(*) [Count]
FROM table1
GROUP BY username, lessonNUm;
there are a lot of rows with the same userName

5

I would suggest learning what each query is doing.

The one you say gives strange results is because it is counting every record that a user has so if the user has the same lesson multiple times it will be counted multiple times.
This is shown by the last query which lists not only the user but the number of times each lesson in the table for that user.

Look into ORDER BY for these simple queries that can help you see the patterns.

6

Please post DDL for Table1:

CREATE TABLE table1(username varchar(50),result sqlvariant ,lessonNum int);
GO

and your data in the form of insert statements for example:

INSERT INTO table1 (userName,result,lessonNum)VALUES ('joe', 'Joe', 1);

And desired result: