# Thread: Select Query To Compare Aggregated Sum From Two Dates

1. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511

## Unanswered: Select Query To Compare Aggregated Sum From Two Dates

Hi,

I have the following SELECT query which gives me sum of price grouped by productcategory:

Code:
```SELECT TableA.ProductCategory, SUM(TableA.Price) As Price

FROM TableA

GROUP BY TableA.ProductCategory```
I can run the same query again using a different business date, say 07-June-2011, and it will give me the Prices from yesterday.

What I would like to do is have a single query which will show me the ProductCategory, the sum of price on the 8th June, the sum price on the 7th June, and a calculated field showing me the difference between the two sum of prices. I had a try using nested SELECT statements but I ended up confusing myself. Please would someone give me some pointers?

Thanks

2. Registered User
Join Date
Nov 2004
Posts
1,428
Try this
Code:
```SELECT T.ProductCategory, T.Price as TodayPrice, Y.Price as YesterdayPrice, (T.Price - Y.Price) as PriceDifference
FROM
(SELECT ProductCategory, SUM(Price) As Price
FROM TableA
GROUP BY ProductCategory) as T -- Today
LEFT OUTER JOIN
(SELECT ProductCategory, SUM(Price) As Price
FROM TableA
GROUP BY ProductCategory) as Y ON -- Yesterday
T.ProductCategory = Y.ProductCategory```

3. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
That's exactly what I wanted.

I'd confused myself on the correct syntax to join T and Y, but your example shows me where I went wrong. Thank you very much!

4. Registered User
Join Date
Nov 2004
Posts
1,428
I wrote another solution to this problem using a CTE. I thought it would be faster than my first attempt. Yet the cost in the execution plan was 1/3th for the first attempt and 2/3th for the CTE solution.

I don't understand why the CTE solution is less efficient.
Code:
```CREATE TABLE TableA(
id	int	not null,
ProductCategory	int not null,
Price	int not null,
)
GO

SELECT T.ProductCategory, T.Price as TodatPrice, Y.Price as YesterdayPrice, (T.Price -  Y.Price) as PriceDifference
FROM
(SELECT ProductCategory, SUM(Price) As Price
FROM TableA
GROUP BY ProductCategory) as T -- Today
LEFT OUTER JOIN
(SELECT ProductCategory, SUM(Price) As Price
FROM TableA
GROUP BY ProductCategory) as Y ON -- Yesterday
T.ProductCategory = Y.ProductCategory
GO

with CTE
AS (
SELECT ProductCategory, BusinessDate, SUM(Price) As Price
FROM TableA
WHERE BusinessDate BETWEEN '07-June-2011' and '08-June-2011'
)
SELECT T.ProductCategory, T.Price as TodatPrice, Y.Price as YesterdayPrice, (T.Price -  Y.Price) as PriceDifference
FROM CTE as T
INNER JOIN CTE as Y ON
T.ProductCategory = Y.ProductCategory AND