SQL- group by two columns, take average and count sample for each case

Question

My table looks like below

x   y    z
a   ab   2
a   ab   4
a   ac   4
b   ab   6
b   ab   4
b   ad   8
c   ac   10
c   ac   10

What I am looking for is this:

x   y    avg_z  number_of_sample
a   ab   3      2
a   ac   4      1
b   ab   5      2
b   ad   8      1
c   ac   10     2

Basically I have right now this:

SELECT x, y, AVG(z) AS avg_z
FROM table1
GROUP BY x, y

But I can't create a next column with number of sample for each case. Do you have idea how I could solve this?

Regards

Answer 1

You can do it as follows :

SELECT x, y, AVG(z) AS avg_z, COUNT(*) AS number_of_sample
FROM table1
GROUP BY x, y