CODEWITHSUNDEEP
sql-server
P. Steiner
P. Steiner

Reputation: 3

Msg 512, Level 16 Error

How can I solve the following problem?

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

I try to get the Frequency like:

| Frequency
| 19
| 23

But somehow I can't get more than I value by firing this Query:

SELECT ts.TimeDifference/ ts.EntryAmount as Frequency
FROM
(
    SELECT COUNT(*) as EntryAmount,
    (
        SELECT DATEDIFF(d, t.minimum, t.maximum)
        FROM (SELECT max(Date) AS maximum, min(Date) AS minimum FROM Times GROUP BY column1, column2, column3, column4 HAVING COUNT(*) > 1) t
    ) AS TimeDifference
    FROM Times
) ts

Thank you.

Upvotes: 0

Views: 532

Answers (2)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

Your innermost sub-select is returning more than one result. You are getting the min and max PER combination of columns 1, 2, 3, and 4. Get rid of the sub-select and it'll resolve the issue.

SELECT
   DATEDIFF(d, ts.minimum, ts.maximum)/ ts.EntryAmount as Frequency
   ,ts.column1, ts.column2, ts.column3, ts.column4
FROM
(
    SELECT
        COUNT(*) as EntryAmount,
        max(Date) AS maximum,
        min(Date) AS minimum,
        column1, column2, column3, column4
    FROM Times
    GROUP BY column1, column2, column3, column4
    HAVING COUNT(*) > 1
) ts

Upvotes: 1

Polux2
Polux2

Reputation: 602

You can use an aggregate function : 

SELECT ts.TimeDifference/ ts.EntryAmount as Frequency
FROM
(
    SELECT COUNT(*) as EntryAmount,
    (
        SELECT Max(DATEDIFF(d, t.minimum, t.maximum))
        FROM (SELECT max(Date) AS maximum, min(Date) AS minimum FROM Times GROUP BY column1, column2, column3, column4 HAVING COUNT(*) > 1) t
    ) AS TimeDifference
    FROM Times
) ts

Upvotes: 0

Related Questions