CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
mituw16
mituw16

Reputation: 5260

T-SQL Group By and Sub Query

I am aware of what the problem is with my query, but am really struggling to find a solution here.

SQL Fiddle

I guess I'm not even really sure how to ask this. What I'm trying to achieve sum all tracking numbers for a date range grouped by branch, but (and this is the kicker) include any other records in the sum that have the same tracking number. I thought of doing something like this, but of course SQL Server doesn't like this because I can't have a subquery in an aggregate function.

MAX((select SUM(demo.NegotiatedRate) where #demo.Tracking = demo2.Tracking)) as NegotiatedRate

Here is the query I have so far if anyone doesnt want to click the SQL Fiddle link

select     demo.Branch, 
           SUM(demo.NegotiatedRate) as NegotiatedRate,
           SUM(demo2.BillRate) as BillRate
from       demo
join       demo2 on demo2.Tracking = demo.Tracking
where      demo.ShipDate = '2014-05-01'
group by   demo.Branch

Expected Output

The output that I am trying to achieve would look something like this. The GH6 negotiated rate and bill rate should match even though one of the GH6 entries falls outside of desired date range. 

Branch    NegotiatedRate    BillRate
GH4       50                50
GH6       25                25

Upvotes: 2

Views: 498

Answers (3)

bamblack
bamblack

Reputation: 3779

As a bit of a simpler answer, you can do something like:

SELECT     demo.Branch, 
           SUM(demo.NegotiatedRate) AS NegotiatedRate,
           demo2.BillRate
FROM       demo
JOIN       demo2 on demo2.Tracking = demo.Tracking
WHERE      demo.Tracking IN
           (
               SELECT Tracking
               FROM demo
               WHERE ShipDate = '2014-05-01'
           )
GROUP BY   demo.Branch, demo2.BillRate

As I understand it, you get all the tracking numbers you want in a certain date range, then get all the information from those tracking numbers, no matter the date range. Then groups them by the Branch and BillRate, both of which should be one value for each tracking number.

Upvotes: 1

StuartLC
StuartLC

Reputation: 107397

You can pre-project the overall (non date-range bound, unfiltered) totals in a separate derived table or cte and then join back to it:

WITH totals AS
(
  SELECT demo.Tracking,
     SUM(demo.NegotiatedRate) as NegotiatedRate
  from       demo
  group by   demo.Tracking
)
select     demo.Branch, 
           MIN(totals.NegotiatedRate) as NegotiatedRate,
           SUM(demo2.BillRate) as BillRate
from       demo
join       demo2 on demo2.Tracking = demo.Tracking
join       totals on totals.Tracking = demo.Tracking
where      demo.ShipDate = '2014-05-01'
group by   demo.Branch;

SqlFiddle here

Given that there should only be one NegotiatedRate per tracking, you can circumvent the need to add the summed totals.NegotiatedRate to the outer query by applying an aggregate (I've used MIN), although this is just to pacify Sql.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271171

If I understand correctly, you want to include all rows when one of the dates is the selected date. If this is correct, then you want the logic in the having clause, not in the where clause:

select     demo.Branch, 
           SUM(demo.NegotiatedRate) as NegotiatedRate,
           SUM(demo2.BillRate) as BillRate
from       demo
join       demo2 on demo2.Tracking = demo.Tracking
group by   demo.Branch
having sum(case when demo.ShipDate = '2014-05-01' then 1 else 0 end) > 0

Upvotes: 0

Related Questions