CODEWITHSUNDEEP
sqlpostgresql
user458
user458

Reputation: 389

Count Values in a column and add the result next to the column in a new one sql

I have the following table

A Home Away 
D Lisa Jill
D Jack Andre
C Jack Kirk
C Jane Jill

and want to get the following result

A Home Away Count
D Lisa Jill 1
D Jack Andre 2
C Jack Kirk 2
C Jane Jill  1

is there a way to do so in sql?

Upvotes: 0

Views: 23

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13049

Use a window function:

select 
     "A", "Home", "Away", 
     count(*) over (partition by "Home") as "Count"
from _table;

Upvotes: 2

romainsalles
romainsalles

Reputation: 2143

You can try something like that:

WITH table_counts AS (
  SELECT
    Home,
    count(*) as table_count
  FROM your_table
  GROUP BY Home
)
SELECT
  t1.A,
  t1.Home,
  t1.Away,
  t2.table_count as Count
FROM your_table t1
JOIN table_counts t2 ON t2.Home = t1.Home;

Upvotes: 0

Related Questions