How to count rows and transfer them to separate columns?-CodePudding

I have table like this:

Castomer_ID Survay_result
John        Good
Ben         Bad
John        Bad
John        Good
Ben         Bad
Ben         Good

I want to create query witch return this result:

Castomer_ID Good    Bad
John        2       1
Ben         1       2

I tried use subquery for this:

SELECT Castomer_ID AS temp_Castomer_ID,
    (SELECT COUNT(Survay_result)
     FROM test1
     WHERE
        temp_Castomer_ID = Castomer_ID
        AND
        template_id = Good) AS 'Good',
    (SELECT COUNT(Survay_result)
     FROM test1
     WHERE
        temp_Castomer_ID = Castomer_ID
        AND
        template_id = Bad) AS 'Bad',
FROM test
GROUP BY 
    Castomer_ID

and I see next error massage:

Msg 207, Level 16, State 1, Line 5
Invalid column name 'temp_Castomer_ID'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'temp_Castomer_ID'.

What am I doing wrong? Help please.

CodePudding user response：

Use table aliases and qualify the columns to resolve name conflicts.

SELECT t.castomer_id,
       (SELECT count(*)
               FROM test AS tg
               WHERE tg.castomer_id = t.castomer_id
                     AND tg.survay_result = 'Good') AS good,
       (SELECT count(*)
               FROM test AS tb
               WHERE tb.castomer_id = t.castomer_id
                     AND tb.survay_result = 'Bad') AS bad
       FROM test AS t
       GROUP BY t.castomer_id;

Also:

The objects in your query and the given sample don't fully match. You'll sort this out yourself.
Don't get used to use single quotes for identifiers such as column aliases. Yes, sadly SQL Server accepts that in some places, but in SQL single quotes are usually for string (or date...) literals. Should you ever use another DBMS (or future SQL Server versions become more sane about this) you'll likely get an error. Use square brackets for identifiers that have special characters in them or are case sensitive or preferably don't use special characters and case sensitive identifiers at all. Identifiers don't need to be "pretty", "pretty" headers and such are a job for the presentation layer.
But you have to quote string literals.

Instead of the subqueries you could also use conditional aggregation. This might perform better, you need to test this.

SELECT t.castomer_id,
       count(CASE
               WHEN t.survay_result = 'Good' THEN
                 1
             END) AS good,
       count(CASE
               WHEN t.survay_result = 'Bad' THEN
                 1
             END) AS bad
       FROM test AS t
       GROUP BY t.castomer_id;

CodePudding user response：

I would suggest you actually just use conditional aggregation with a JOIN:

SELECT t.Castomer_ID AS temp_Castomer_ID, --Customer doesn't have an a
       COUNT(CASE t1.Survay_result WHEN 'Good' THEN 1 END) AS Good, --Don't use literal strings for aliases. Survey doesn't have an a
       COUNT(CASE t1.Survay_result WHEN 'Bad' THEN 1 END) AS Bad --Don't use literal strings for aliases. Survey doesn't have an a
FROM dbo.test t
     LEFT JOIN dbo.test1 t1 ON t.Castomer_ID = t1.Castomer_ID
GROUP BY t.Castomer_ID;