CODEWITHSUNDEEP
sqljoin
Justin Leveck
Justin Leveck

Reputation: 2358

SQL: LEFT JOIN SELECT STATEMENT to addd "Daily Sales Total" Column

I am trying to create a query that will show the details for each sale along with another column that will show the total daily sales that occurred on the day the order was created (creation date)

I have two queries that deliver the desired information but I am having trouble merging them. I believe a LEFT JOIN SELECT Statement is necessary to achieve this.

### Query that displays order details ###

SELECT DISTINCT
Tracking.ID,
Tracking.CreationDate,
Tracking.CurrentAssignedDriverID, 
Tracking.TotalCost, 
Tracking.CustomerID, 
Tracking.dFrom, 
Tracking.dTo,
(SELECT Company FROM Customers WHERE Customers.ID = CustomerID)
FROM Tracking

### Query that yields daily totals ###

SELECT 
CAST(convert(char(10), Tracking.CreationDate, 101) AS smalldatetime),
Tracking.TotalCost

FROM

Tracking

GROUP BY

CAST(convert(char(10), Tracking.CreationDate, 101) AS smalldatetime),
Tracking.TotalCost

Upvotes: 0

Views: 1082

Answers (1)

Chandu
Chandu

Reputation: 82933

You can get all the information in one query as given below:

SELECT  Tracking.ID,
        Tracking.CreationDate,
        Tracking.CurrentAssignedDriverID, 
        Tracking.TotalCost, 
        Tracking.CustomerID, 
        Tracking.dFrom, 
        Tracking.dTo,
        Customers.Company,
        SUM(Tracking.TotalCost) OVER(PARTITION BY CAST(convert(char(10), Tracking.CreationDate, 101) AS smalldatetime))
        AS TotalSales
  FROM  Tracking INNER JOIN Customers 
      ON    Customers.ID = Tracking.CustomerID

Upvotes: 1

Related Questions