SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trying to add an aggregated subquery
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NigelJB
Starting Member

United Kingdom
2 Posts

Posted - 02/26/2014 :  10:48:51  Show Profile  Reply with Quote
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

USA
11 Posts

Posted - 02/26/2014 :  11:12:19  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/27/2014 :  02:44:35  Show Profile  Reply with Quote
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
Go to Top of Page

NigelJB
Starting Member

United Kingdom
2 Posts

Posted - 02/27/2014 :  05:58:15  Show Profile  Reply with Quote
Thanks kennejd. That worked a treat.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000