Avatar of uswebpro9
uswebpro9
Flag for Czechia

asked on 

SQL Query with an inner query?

Hello, I have one SQL statement. (See Fig 1) I use this to make a view. I have another SQL statement (see fig 2) to group by the order dates.
Is there a way to combine both statements into one?

Below is something I tried, with no luck. thanks 4 any help.
SELECT [orderDate]
      ,sum([DailyRevenue]) as DailyRevenue
      ,count([TotalOrders]) as TotalOrders
      ,[ShowId]
      ,[Title]
  FROM (
SELECT     
	dbo.DateOnly(dbo.v_SalesPerEvent.orderDate) AS orderDate, 
	SUM(dbo.v_SalesPerEvent.PricePaid) AS DailyRevenue, 
	COUNT(dbo.v_SalesPerEvent.OrderNumber) AS TotalOrders, 
	dbo.v_SalesPerEvent.ShowId, dbo.tb_Show.Title
FROM         dbo.v_SalesPerEvent INNER JOIN
                      dbo.tb_Show ON dbo.v_SalesPerEvent.ShowId = dbo.tb_Show.showId
GROUP BY dbo.v_SalesPerEvent.ShowId, dbo.v_SalesPerEvent.orderDate, dbo.tb_Show.Title
)
group by [orderDate],[ShowId],[Title]
order by [orderDate]

Open in new window

Microsoft SQL ServerDatabases

Avatar of undefined
Last Comment
BrandonGalderisi

8/22/2022 - Mon