Question 218081 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    Short Answer

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

    Long Answer (How query is executed by the database)

    Assume we have a table named data with the following data:

    x y
    A 1
    B 2
    A 1
    B 3
    C 2

    Step 1: Data Retrieval

    The database engine retrieves the data from the data table.

    Step 2: Grouping

    The data is grouped based on unique combinations of values in columns x and y. The groups are as follows:

    Group 1: (x=’A’, y=1)

    x y
    A 1
    A 1

    Group 2: (x=’B’, y=2)

    x y
    B 2

    and so one for the rest of the values

    Step 3: Aggregation

    For each group, the COUNT(*) function is applied to calculate the number of occurrences within each group:

    Group 1: (x=’A’, y=1), COUNT(*) = 2

    Group 2: (x=’B’, y=2), COUNT(*) = 1

    and so on

    Step 4: Result Generation

    The final result set is generated, combining the grouped columns (x and y) with the calculated occurrence_count for each group:

    x y occurrence_count
    A 1 2
    B 2 1
    B 3 1
    C 2 1

    Step 5: Query Completion

    The result set is presented to the user or application that requested the data.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search