Getting error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." for comparing sales accounts

Question

I am getting the following error when trying to compare 2 columns to another 2 columns based on a sales employee.

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

My code is this:

select  T1.CardCode 'BP Code',
        T1.CardName 'BP Name',
        COUNT(T0.DocNum) '# of Orders',
        SUM(T0.DocTotal) 'Total Orders Amt',
        SUM(T0.DOCTOTAL)/COUNT(T0.DOCNUM) 'Avg Order Size',
        (SELECT COUNT(T0.DocNum),
        SUM(T0.DocTotal),
        SUM(T0.DOCTOTAL)/COUNT(T0.DOCNUM)

from    ORDR T0 INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode

where   T0.DocStatus = 'C'
        AND T0.CANCELED <> 'Y'
        AND T0.DocDate >= '2015-05-26'
        AND T1.SlpCode = '37'

GROUP BY    T1.CardCode,
            T1.CardName) 

from    ORDR T0 INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode

where   T0.DocStatus = 'C'
        AND T0.CANCELED <> 'Y'
        AND T0.DocDate >= '2015-05-26'
        AND T1.SlpCode <> '37'

GROUP BY    T1.CardCode,
            T1.CardName

I want to compare customer accounts pre-new employee versus post new sales employee from their employment date of this year. So I want to have 6 columns.

3 Columns of [# of Orders], [Total order amt], [avg order size] generated by the current account holder vs 3 columns of [# of Orders], [Total order amt], [avg order size] generated by sales employee "37".

Any suggestions on rearranging this code to achieve what I am looking for??

Answer 1

I think just querying the tables once should suffice:

select  T1.CardCode 'BP Code',
        T1.CardName 'BP Name',
        COUNT(CASE WHEN T1.SlpCode<>'37' THEN T0.DocNum END) '# of Orders',
        SUM(CASE WHEN T1.SlpCode<>'37' THEN T0.DocTotal END) 'Total Orders Amt',
        SUM(CASE WHEN T1.SlpCode<>'37' THEN T0.DOCTOTAL END)/
          COUNT(CASE WHEN T1.SlpCode<>'37' THEN T0.DocNum END) 'Avg Order Size',
        COUNT(CASE WHEN T1.SlpCode='37' THEN T0.DocNum END),
        SUM(CASE WHEN T1.SlpCode='37' THEN T0.DocTotal END),
        SUM(CASE WHEN T1.SlpCode='37' THEN T0.DOCTOTAL END)/
          COUNT(CASE WHEN T1.SlpCode='37' THEN T0.DocNum END),

from    ORDR T0 INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode

where   T0.DocStatus = 'C'
        AND T0.CANCELED <> 'Y'
        AND T0.DocDate >= '2015-05-26'

GROUP BY    T1.CardCode,
            T1.CardName

You might also want to remove the columns which calculate the average here and perform that processing as a later step in e.g. the code that consumes this result set, so that you don't have to deal with division by zero, if that's a risk.