CODEWITHSUNDEEP
sqlsql-server
Thomas Tl
Thomas Tl

Reputation: 35

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. SQL

I am trying to run my query but return error : Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. 

    Select 
    u.*,
    (
        Select 
            COUNT(cDays) as c,
            SUM(un) as un,
            SUM(vcs) as vcs
        From
        (
            Select
                s.DateSales as cDays,
                SUM(s.Un) as Un,
                SUM(s.VCS) as vcs
            From
                dbSales.dbo.Sales s
            Where
                s.IDX = u.IDX And s.DateSales Between DATEADD(dd,-8, u.DateS) And DATEADD(dd,-1, u.DateS)
            Group by
                s.DateSales
        ) as sct
    )

From 
    unikat u
)

Upvotes: 0

Views: 213

Answers (3)

EzLo
EzLo

Reputation: 14199

Use the APPLY operator for the subquery:

Select 
    u.*,
    sct.*
From 
    unikat u
    OUTER APPLY 
    (
        Select
            COUNT(DISTINCT(s.DateSales)) as cDays,
            SUM(s.Un) as Un,
            SUM(s.VCS) as vcs
        From
            dbSales.dbo.Sales s
        Where
            s.IDX = u.IDX And 
            s.DateSales Between DATEADD(dd,-8, u.DateS) And DATEADD(dd,-1, u.DateS)
    ) AS sct

You can incorporate any amount of columns resulting from the APPLY operator. Use CROSS APPLY instead of OUTER APPLY if you want to filter NULL matching results.

Upvotes: 1

Lord Dafaq
Lord Dafaq

Reputation: 31

Try this:

Select *
  From unikat u, (
    Select 
        COUNT(cDays) as c,
        SUM(un) as un,
        SUM(vcs) as vcs
    From
    (
        Select
            s.DateSales as cDays,
            SUM(s.Un) as Un,
            SUM(s.VCS) as vcs
        From
            dbSales.dbo.Sales s
        Where
            s.IDX = u.IDX And s.DateSales Between DATEADD(dd,-8, u.DateS) 
            And DATEADD(dd,-1, u.DateS)
        Group by
            s.DateSales
    ) 
) a

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13016

add select top 1

  Select 
    u.*,
    (
        Select top 1
            COUNT(cDays) as c,
            SUM(un) as un,
            SUM(vcs) as vcs
        From
        (
            Select
                s.DateSales as cDays,
                SUM(s.Un) as Un,
                SUM(s.VCS) as vcs
            From
                dbSales.dbo.Sales s
            Where
                s.IDX = u.IDX And s.DateSales Between DATEADD(dd,-8, u.DateS) And DATEADD(dd,-1, u.DateS)
            Group by
                s.DateSales
        ) as sct
    )

From 
    unikat u
)

Upvotes: 0

Related Questions