Subconsulta returns more than one value

Question

Subconsulta returns more than one value

Asked 4 years ago

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

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.

– anonimo

2020/12/09 at 21:47
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)

– Almino

2020/12/09 at 21:58
I tried to put a TOP 1 but it didn’t solve

– Almino

2020/12/09 at 21:59
Evaluate the use of the GREATEST function of these COUNT.

– anonimo

2020/12/09 at 22:01
In SQL SERVER there is no such function. Know any alternative?

– Almino

2020/12/09 at 22:10
Do you want the highest value Emailpromotion (count) per person (person) ? Give an example of the required data and output.

– Motta

2020/12/10 at 00:02

Show 1 more comment

1 answer

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

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

by imex • **1,387** points · Answer 1 · 2020-12-10T12:31:42+00:00

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