Trying to add an aggregated subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying to add an aggregated subquery

Author

Topic

NigelJB
Starting Member

2 Posts

Posted - 2014-02-26 : 10:48:51

Hi,

Very new to SQL and trying to get this query to run.
I need to sum the total trips and total values as separate columns by day to insert them into another table.....HELP PLEASE!!!

My code is as follows;

Insert Into [dbo].[CombinedTripTotalsDaily]
(
Year,
Month,
Week,
DayNo,
Day,
Trip_Date,
Total_Trips,
Total_Yield
)
Select
d.CYear as Year,
d.CMonth as Month,
d.CWeek as Week,
d.DayNo as DayNo,
d.Day as Day,
d.date as Trip_Date,
Total_Trips = SUM(
(Select SUM(XTrip_Totals) from (Select sum(trips) XTrip_Totals from [dbo].[ConcessionTripValues] where Trip_Date > '01 Nov 2011' group by Trips) as sub)+
(Select SUM(XTrip_Totals) from (Select sum(trips) XTrip_Totals from [dbo].[DailyTVMTicketYield] where Trip_Date > '01 Nov 2011' group by Trips) as sub)+
(Select SUM(XTrip_Totals) from (Select sum(trips) XTrip_Totals from [dbo].[KangarooTripValues] where Trip_Date > '01 Nov 2011' group by Trips) as sub)+
(Select SUM(XTrip_Totals) from (Select sum(trips) XTrip_Totals from [dbo].[SeasonTicketDailyYield] where Trip_Date > '01 Nov 2011' group by Trips) as sub)
)
,
Total_Yield = SUM(
(Select SUM(XValue_Totals) from (Select sum(Trip_Value) XValue_Totals from [dbo].[ConcessionTripValues] where Trip_Date > '01 Nov 2011' group by Trip_Value) as sub)+
(Select SUM(XValue_Totals) from (Select sum(Trip_Value) XValue_Totals from [dbo].[DailyTVMTicketYield] where Trip_Date > '01 Nov 2011' group by Trip_Value) as sub)+
(Select SUM(XValue_Totals) from (Select sum(Trip_Value) XValue_Totals from [dbo].[KangarooTripValues] where Trip_Date > '01 Nov 2011' group by Trip_Value) as sub)+
(Select SUM(XValue_Totals) from (Select sum(Trip_Value) XValue_Totals from [dbo].[SeasonTicketDailyYield] where Trip_Date > '01 Nov 2011' group by Trip_Value) as sub)
)
From[dbo].[Date] as d
join [dbo].[ConcessionTripValues] as c
on d.date = c.trip_date
join [dbo].[DailyTVMTicketYield] as t
on d.date = t.trip_date
join [dbo].[KangarooTripValues] as k
on d.date = k.trip_date
join [dbo].[SeasonTicketDailyYield] as s
on d.date = s.trip_date
where d.date > '01 Nov 2011'
group by d.cyear,d.cmonth,d.cweek,d.dayno,d.day,d.date
;

kennejd
Starting Member

11 Posts

Posted - 2014-02-26 : 11:12:19

I think maybe a union query, or a series of inserts, might be easier...and more efficient...:) Something like (I'm ignoring the insert part):

select d.CYear as Year, d.CMonth as Month, d.CWeek as Week, d.DayNo as DayNo, d.Day as Day, d.date as Trip_Date, sum(trips), sum(trip_value) from concessiontripvalues c inner join [date] d on d.date = c.trip_date where....

union all

select d.CYear as Year, d.CMonth as Month, d.CWeek as Week, d.DayNo as DayNo, d.Day as Day, d.date as Trip_Date, sum(trips), sum(trip_value) from DailyTVMTicketYield c inner join [date] d on d.date = c.trip_date where....

union all
etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-27 : 02:44:35

Show us some sample data and explain how you want output to perform.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

NigelJB
Starting Member

2 Posts

Posted - 2014-02-27 : 05:58:15

Thanks kennejd. That worked a treat.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources