how to get GROUP data from multiple tables msSQL

Question

tblDailyProduction
+---------+------------+------------+------------+----------+----------+
| date |      items | quantity| weight| wheatConsumed| |
+---------+------------+------------+------------+----------+----------+
tblCashBillbook
+---------+------------+------------+------------+----------+----------+
+---------+------------+------------+------------+----------+----------+
| date |      bagWeight | totalBags |
+---------+------------+------------+------------+----------+----------+



SELECT CAST(tblDailyProduction.date AS DATE) as DateField,
SUM(tblDailyProduction.quantity * tblDailyProduction.weight) 
AS   totalProduct,SUM(tblCashBillbook.totalBags * tblCashBillbook.bagWeight) 
AS   totalIssued
FROM tblDailyProduction
left join tblCashBillbook on tblDailyProduction.date=tblCashBillbook.date
GROUP BY CAST(tblDailyProduction.date AS DATE)

I want to Group data according to the date from two different tables using a JOIN clause. Getting the data from tblDailyProduction table but NULL from the tblCashBillBook table. tried INNER, LEFT and RIGHT joins but there is a problem with the GROUP BY clause.

Answer 1

You don't need to group by both dates, as the dates are put together in your join statement.

Try changing it to:

GROUP BY dateField

What might be causing some problem is that you are doing a left join, so not every row will have a tblCashBillbook.date value. If they do, you shouldn't be using a left join but an inner join. So, you are trying to group by something that might be null in some rows, so again, I suggest you just group by the tblDailyProduction date column.