CODEWITHSUNDEEP
sql
Bradley
Bradley

Reputation: 2141

SQL: How do I return a column that is an aggregate of the rows returned on another column?

I have a table of Companies and Invoices. My query sums the total values of each companies invoices to provide a 'total sales' by company. What I want to return is the +/- of each companies sales compared to the other companies in the same query.

Select Company.name, sum(Invoice.total) as InvoiceTotal
From Company
Inner Join Invoice on Invoice.CompanyId = Company.Id
Group By Company.name

Assuming The Above Query Produces:

Company          InvoiceTotal
A                100
B                200
C                600

I want the additional column to provide the amount over or under the average of the InvoiceTotal column:

Company          InvoiceTotal    Difference
A                100             -200
B                200             -100
C                600             300

How can I extract that value in a single query?

Upvotes: 1

Views: 81

Answers (4)

M. Arnold
M. Arnold

Reputation: 425

The results can be achieved using the query below:

Select Company.company, sum(Invoice.total) as InvoiceTotal, (SELECT AVG(Invoice.total) FROM Invoice) AS InvoiceAvg,(SELECT AVG(Invoice.total) FROM Invoice) -sum(Invoice.total) AS Difference
From Company
Inner Join Invoice on Invoice.CompanyId = Company.Id
Group By Company.company

Upvotes: 0

Eponyme Web
Eponyme Web

Reputation: 976

Here's another way to get the variance you're looking for

Select
    name,
    InvoiceTotal,
    InvoiceTotal - avg(InvoiceTotal) as Difference
From
    (Select Company.name, sum(Invoice.total) as InvoiceTotal
    From Company
    Inner Join Invoice on Invoice.CompanyId = Company.Id
    Group By Company.name)
group by name, InvoiceTotal

Upvotes: 0

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

Assuming your database supports CTE

with total as (
    select avg(invoicetotal) totalsum
from table_name)
    select t.company, 
           t.invoicetotal, 
           t.invoicetotal - total.totalsum
from table_name t, total

Company INVOICETOTAL    T.INVOICETOTAL-TOTAL.TOTALSUM
A        100            -200
B        200            -100
C        600             300

Upvotes: 1

EzLo
EzLo

Reputation: 14199

If your database can handle windowed averages (most do), you can use a windowed average function thats applied to the sum of invoices, then do the rest with the sum of invoice.

This is an example with SQL Server:

;WITH Data AS
(
    SELECT
        *
    FROM
        (
        VALUES
            ('a', 50),
            ('a', 25),
            ('a', 25),

            ('b', 125),
            ('b', 75),

            ('c', 275),
            ('c', 50),
            ('c', 75),
            ('c', 200)) V(Company, Invoice)
)
SELECT
    Company = V.Company,
    Invoice = SUM(V.Invoice),
    AverageAcrossAllCompanies = AVG(SUM(V.Invoice)) OVER (),
    AverageInvoiceDifference = SUM(V.Invoice) - AVG(SUM(V.Invoice)) OVER ()
FROM
    Data AS V
GROUP BY
    V.Company

Results:

Company Invoice AverageAcrossAllCompanies   AverageInvoiceDifference
a       100     300                         -200
b       200     300                         -100
c       600     300                         300

Upvotes: 1

Related Questions