CODEWITHSUNDEEP
sqlsql-servergroup-by
rcardinaux
rcardinaux

Reputation: 231

Group by 3 columns: "Each group by expression must contain at least one column that is not an outer reference"

I know questions regarding this error message have been asked already, but I couldn't find any that really fit my problem.

I have a table with three columns (A,B,C) containing different values and I need to identify all the identical combination. For example out of "TABLE A" below:

| A | B | C |
| 1 | 2 | 3 |
| 1 | 3 | 3 |
| 1 | 2 | 3 |
| 2 | 2 | 2 |
| 1 | 3 | 3 |

... I would like too get "TABLE B" below:

| A | B | C | count |
| 1 | 2 | 3 |   1   |
| 1 | 3 | 3 |   1   |
| 2 | 2 | 2 |   1   |

(I need the last column "count" with 1 in each row for later usage)

When I try with "group by A,B,C" I get the error mentioned in the title. Any help would be greatly appreciated!

FYI, I don't think it really changes the matter, but "TABLE A" is obtained from an other table: "SOURCE_TABLE", thanks to a query of the type:

select (case when ... ),(case when ...),(case when ...) from SOURCE_TABLE

and I need to build "TABLE B" with only one query.

Upvotes: 0

Views: 120

Answers (2)

Sturgus
Sturgus

Reputation: 686

Sounds like you have the right idea. My guess is that the error is occurring due to an outer reference in your CASE statements. If you wrapped your first query in another query, it may alleviate this issue. Try:

SELECT A, B, C, COUNT(*) AS [UniqueRowCount]
FROM (
    SELECT (case when ... ) AS A, (case when ...) AS B, (case when ...) AS C FROM SOURCE_TABLE
) AS Subquery
GROUP BY A, B, C

After re-reading your question, it seems that you're not counting at all, just putting a "1" after each distinct row. If that's the case, then you can try:

SELECT DISTINCT A, B, C, [Count]
FROM (
    SELECT (case when ... ) AS A, (case when ...) AS B, (case when ...) AS C, 1 AS [Count] FROM SOURCE_TABLE
) AS Subquery

Assuming your outer reference exceptions were occurring in only your aggregations, you should also simply try:

SELECT DISTINCT (case when ... ) AS A, (case when ...) AS B, (case when ...) AS C, 1 AS [Count] FROM SOURCE_TABLE

Upvotes: 0

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

i think what you are after of is using distinct

select distinct A,B,C, 1 [count]   -- where 1 is a static value for later use
    from (select ... from  sourcetable) X

Upvotes: 1

Related Questions