CODEWITHSUNDEEP
sqlt-sql
stackuser
stackuser

Reputation: 672

How to aggregate the result of an expression?

I am trying to get the sum(docBal) for each company after adjusting the docBal. But when I run the query below, I am forced to include doctype and docbal in the groupby- which I dont want. How can I modify this query?

 select 
  DocBal =  CASE d.DocType WHEN 'AD' THEN -d.DocBal
                           ELSE d.DocBal END,
  Sum(DocBal)
  from Vendor v
   inner join APDoc d
   on v.VendId = d.VendId
   where 
      d.PerPost = 201307
      and d.DocType in ('VO','AP','AD')
      and d.OpenDoc = 1
      and Acct = 210110
     group by CpnyID

Upvotes: 2

Views: 95

Answers (4)

Andriy M
Andriy M

Reputation: 77707

You are not saying what SQL product (and which version) you are using. It is true that the derived table solution that others have suggested would work in any product, but in case you are working with SQL Server 2005 or later version, you could also use CROSS APPLY, like this:

SELECT
  CpnyID,  -- assuming you would like to know which SUM() belongs to which CpnyID
  SUM(x.DocBal)
FROM Vendor AS v
INNER JOIN APDoc AS d ON v.VendId = d.VendId
CROSS APPLY (
  SELECT DocBal = CASE d.DocType WHEN 'AD' THEN -d.DocBal ELSE d.DocBal END
) AS x
WHERE d.PerPost = 201307
  AND d.DocType in ('VO','AP','AD')
  AND d.OpenDoc = 1
  AND Acct = 210110
GROUP BY
  CpnyID
;

There's also a much simpler, in my opinion, solution: you could make the entire CASE expression the argument of SUM, like this:

SELECT
  CpnyID,
  SUM(CASE d.DocType WHEN 'AD' THEN -d.DocBal ELSE d.DocBal END)
FROM Vendor AS v
INNER JOIN APDoc AS d ON v.VendId = d.VendId
WHERE d.PerPost = 201307
  AND d.DocType in ('VO','AP','AD')
  AND d.OpenDoc = 1
  AND Acct = 210110
GROUP BY
  CpnyID
;

Upvotes: 2

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

select sum(DocBal) FROM
(select 
  DocBal =  CASE d.DocType WHEN 'AD' THEN -d.DocBal
                           ELSE d.DocBal END,
  CpnID
  from Vendor v
   inner join APDoc d
   on v.VendId = d.VendId
   where 
      d.PerPost = 201307
      and d.DocType in ('VO','AP','AD')
      and d.OpenDoc = 1
      and Acct = 210110) a
GROUP BY CpnID

Upvotes: 1

Richard Newman
Richard Newman

Reputation: 630

You can use a sub-select to capture the information you require.

    SELECT  S.CpnyID,
            SUM(S.DocBal)
    FROM    (
            SELECT  CpnyID,
                    CASE d.DocType WHEN 'AD' THEN -d.DocBal ELSE d.DocBal END AS DocBal
            FROM    Vendor v
                    INNER JOIN APDoc d ON v.VendId = d.VendId
            WHERE     d.PerPost = 201307
            AND d.DocType in ('VO','AP','AD')
            AND d.OpenDoc = 1
            AND Acct = 210110
    ) AS S
    GROUP BY S.CpnyID

This should give you the data you require without having to include the additional columns in the aggregate function.

Upvotes: 2

jny
jny

Reputation: 8067

    select sum(DocBal) from (
        select 
          DocBal =  CASE d.DocType WHEN 'AD' THEN -d.DocBal
                                   ELSE d.DocBal END,
          CpnyID
          from Vendor v
           inner join APDoc d
           on v.VendId = d.VendId
           where 
              d.PerPost = 201307
              and d.DocType in ('VO','AP','AD')
              and d.OpenDoc = 1
              and Acct = 210110
      )
  group by CpnyID

Upvotes: 0

Related Questions