SQL Server: SUM multiple rows depending on a conditon

Question

My table looks like this (I work at an energy company)

dte             hub            Peak            Offpeak
07-04-2017      SoCo           36.19           18.23
07-04-2017      SwapBID        0.5             0.25
07-05-2017      SoCo           32.12           21.72
07-05-2017      SwapBID        0.25            0.25
07-06-2017      SoCo           33.87           20.34
07-06-2017      SwapBID        0.5             0.5

I want to be able to pull the data from this table using a select query and make it so that the two hubs' peak and offpeak prices are added together for the same days. For example, on the day 07/04/2017 i want it to show that the Peak was 36.69 and the Offpeak was 18.48. I would like to do this for every day in the table. How do I code this?

WIth that sample data, what is the expected result - as formatted text? — jarlh, Jul 07 '17 at 14:23
You should post what you've tried - and any research you've done. — Stan Shaw, Jul 07 '17 at 14:25

score 1 · Answer 1 · answered Jul 07 '17 at 14:24

Just aggregate your table by date and sum the peak and offpeak values:

SELECT
    dte,
    SUM(Peak) AS Peak,
    SUM(Offpeak) AS Offpeak
FROM yourTable
GROUP BY dte

If a given date could have more than one hub type but we wanted to only consider SoCo and SwapBID hubs, then we could consider adding a WHERE clause to the query:

WHERE hub IN ('SoCo', 'SwapBID')

score 0 · Answer 2 · answered Jul 07 '17 at 14:26

You're looking for aggregationsgroup by

Try :

create table #t(dte date,hub varchar(100),Peak float,Offpeak float)
insert into #t values
('07-04-2017','SoCo',36.19,18.23),
('07-04-2017', 'SwapBID',0.5,0.25),
('07-05-2017','SoCo',32.12,21.72),
('07-05-2017','SwapBID',0.25,0.25),
('07-06-2017', 'SoCo,33.87,20.34),
('07-06-2017', 'SwapBID',0.5,0.5 )

select dte,
sum(Peak) as total_peak,
sum(Offpeak) as total_offpeak
 from #t
group by dte

SQL Server: SUM multiple rows depending on a conditon

2 Answers2