CODEWITHSUNDEEP
sqlms-access
Александр Черноусов
Александр Черноусов

Reputation: 13

Trouble with sql columns

I've got 2 tables in Database (MS Access) (Fruits and Vegetables). Each of them has fields "Date" and "Value". It is possible to have a few rows with the same date. How can I output a table which will show for each row "Date", "Sum of Values for Fruits" and "Sum of Values for Vgetables"? And, for example, if there is no "Value" for "Vegetables" for some "Date", then the empty cell will present?

Example is on the screenshot

Upvotes: 0

Views: 38

Answers (2)

nAviD
nAviD

Reputation: 3281

SELECT Date,Sum(fValue) as FruitsValue,Sum(vValue) as VegtablesValue
FROM
(
SELECT Date,value as fValue,null as vValue FROM Fruits 
UNION ALL SELECT Date,null as fValue,Value as vValue  FROM Vegetables
) T
GROUP BY Date

Upvotes: 1

Javasick
Javasick

Reputation: 3003

Try something like this (not tested):

SELECT t.date, SUM(f_val) as f_val, SUM(v_val) AS v_val
FROM (
    SELECT date, value AS f_val, 0 AS v_val FROM Fruits 
    UNION ALL
    SELECT date, 0, value FROM Vegetables
) t
GROUP BY t.date

Upvotes: 1

Related Questions