Subconsulta returns more than one value

Asked

Viewed 120 times

-2

Good night!

I have an sql query based on a CTE and I want to take the largest value between a subconsulta, but when I run the main query the subconsulta error returns more than one value appears. Could you help me?

Follows code:

WITH CTE AS(
            SELECT TOP 1 PERSON.PERSON.FirstName AS NOME,
        (SELECT COUNT(*) AS A FROM PERSON.PERSON
        WHERE EmailPromotion = 0

        UNION ALL 

        SELECT COUNT(*) FROM PERSON.PERSON
        WHERE EmailPromotion = 1) AS EMAIL
        
        FROM PERSON.PERSON)SELECT MAX(EMAIL) FROM CTE
sql sql-server query cte union-all

  • Try to explain which result you want to get because this query is strange. The subselect with UNION ALL will return two lines to the first with the total number of records of the PERSON.PERSON table with Emailpromotion = 0 and the second with the quantity with Emailpromotion = 1.

  • I want to get the highest value between Emailpromotion = 1 and Emailpromotion = 0. I made a Union all to based on that get the highest value (MAX)

  • I tried to put a TOP 1 but it didn’t solve

  • Evaluate the use of the GREATEST function of these COUNT.

  • In SQL SERVER there is no such function. Know any alternative?

  • Do you want the highest value Emailpromotion (count) per person (person) ? Give an example of the required data and output.

Show 1 more comment

1 answer

2


Good morning,

Almino, if you want to get the highest Count of Emailpromotion per person, try doing some tests this way:

WITH CTE_Count AS
(
  SELECT 
    FirstName,
    COUNT(CASE WHEN EmailPromotion = 0 THEN 1 END) AS EP0,
    COUNT(CASE WHEN EmailPromotion = 1 THEN 1 END) AS EP1
  FROM PERSON.PERSON
  GROUP BY
    FirstName
)

SELECT
  FirstName AS NOME,
  CASE WHEN EP0 > EP1 THEN EP0 ELSE EP1 END
FROM CTE_Count

In CTE the counts are made and in the final part of the query is used Case to get the highest count between the 2.

I hope it helps

  • Perfect thank you very much!

Browser other questions tagged sql sql-server query cte union-all

You are not signed in. Login or sign up in order to post.