CODEWITHSUNDEEP
sql-serversumsubquery
Mazda
Mazda

Reputation: 85

MSSQL Sum total on dates from table 1 and table 2

I have this following problem:

I have two tables 1 table for customer bought items and the other for employee sales:

Table 1 (Emp)
EmpId 
ProductId
dateSold
Price
Qty

Table 2 (Cust)
CustomerId
ProductId
dateSold
Price
Qty

do this is the query im tring at the moment:

SELECT  SUM(EmployeeSales.productPrice * EmployeeSales.Qty + 
            userBoughtItems.ProductPrice * userBoughtItems.qty) AS Total, 
        EmployeeSales.dateSold, 
        userBoughtItems.dateSold AS Expr1
FROM    EmployeeSales 
INNER JOIN userBoughtItems ON EmployeeSales.dateSold = userBoughtItems.dateSold
GROUP BY EmployeeSales.dateSold, userBoughtItems.dateSold

I want to get the total of both tables on the same date...

Upvotes: 0

Views: 1412

Answers (2)

Andomar
Andomar

Reputation: 238078

A join repeats the right table for every matching row in the left table. In your example I'd expect that to result in a lot of duplicated rows. Consider using a union instead:

select  cast(dt as date)
,       sum(sales)
from    (
        select  dateSold as dt
        ,       productPrice * Qty as sales
        from    EmployeeSales
        union all
        select  dateSold
        ,       ProductPrice * qty
        from    userBoughtItems
        ) as SubQueryAlias
group by
        cast(dt as date)

The subquery contains a list of all sales, and the outer query sums them per day.

If you're using SQL Sever 2005 or older, replace cast(dt as date) with dateadd(dd, 0, datediff(dd, 0, dt)).

Upvotes: 1

Saranya Jothiprakasam
Saranya Jothiprakasam

Reputation: 294

Without group by clause it could n't works it throws the following error.

Msg 8120, Level 16, State 1, Line 4 Column 'employeesales.dateSold' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I changed slightly,

Use this

select dt , sum(sales) from ( select dateSold as dt , sum(Price * Qty) as sales from employeesales group by dateSold union all select dateSold , sum(Price * qty) from customerboughtitems group by dateSold ) as SubQueryAlias group by dt

Upvotes: 1

Related Questions