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.
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_datewhere 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 allselect 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 alletc. |
|
|
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 MVPhttp://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. |
|
|
|
|
|
|
|