Get count of all types of values in a column obtained in the same SELECT SQL query-CodePudding

I'm attempting to minimize the amount of queries I have to execute.

Right now, I'm executing a query similar to this:

SELECT
    TABLE 1.column1 as "A",
    TABLE 1.column2 as "B"

FROM 
    TABLE 1
WHERE 
    CONDITION

I can obtain the results I need from this query, however I would also like to obtain the count of what type of values show up in the same query.

For example, if the following query retrieves this table

A   B
-   -
1   a
1   b
1   c
2   d
3   e
4   f
4   g

I would also like to, for each row, obtain the count of all rows retrieved with its column "A" that matches its value.

Would it be more efficient to execute another query to get that result or can I modify my obtaining query to get this statistic?

Desired result:

A   B   C
-   -   -
1   a   3   # 3 rows with "1" in Column "A"
1   b   3
1   c   3
2   d   1
3   e   1
4   f   2
4   g   2

UPDATE:

The closest query I could find goes like this:

SELECT
    TABLE 1.column1 as "A",
    COUNT(TABLE 1.column1)
FROM 
    TABLE 1
WHERE 
    TABLE 1.column1 = "foo"
GROUP BY
    TABLE 1.column1

Results in this:

However, it removes any other rows with the same value in column "A". Even if it has different values in column "B". I would like to have all rows present in my SQL query with its corresponding row count.

The next closest query I found goes like this:

SELECT
    TABLE 1.column1 as "A",
    COUNT(TABLE 1.column1)
FROM 
    TABLE 1
WHERE 
    TABLE 1.column1 = "foo"
GROUP BY
    TABLE 1.column2

Results in this:

Which also isn't achieving the desired result.

CodePudding user response：

You have to join with the subquery that gets the counts.

SELECT t1.column1 AS A, t1.column2 AS B, t2.count
FROM Table1 AS t1
JOIN (
    SELECT column1 AS A, COUNT(*) AS count
    FROM Table1
    GROUP BY column1
) AS t2 ON t1.A = t2.A

CodePudding user response：

SELECT
  c,
  COUNT(*) OVER (PARTITION BY c)
FROM t
ORDER BY c