Valkyrja Valkyrja - 1 year ago 51
MySQL Question

Count all records including the row with no values and on two tables

what im planning to do was that it will get all records from the past month from two table(tblshoes, tblpants) and count how many orders happened as this will be used on a google chart line chart i am trying to get all data from july 30 to 1

SELECT COUNT(*) AS shoeorder, EXTRACT(DAY FROM tlDate) as pdate
FROM tblshoes
WHERE tlDate >= CURRENT_DATE - INTERVAL 1 MONTH
GROUP BY EXTRACT(DAY FROM tlDate)


Here is what my tables looks like :

tblshoes

sname tlDate
Name Here 2017-07-15
Name Again 2017-07-14
Name again and again 2017-07-13
Name im tired 2017-07-15
Name of a patient 2017-07-10
.....

tblpants

pname tlDate
Name Here 2017-07-15
Name Again 2017-07-14
Name again and again 2017-07-13
Name im tired 2017-07-15
Name of a patient 2017-07-10


Result:

shoeorder pdate
2 15
1 14
1 13
1 10


What i want:

shoeorder pdate pantorder
0 30 0
0 29 0
0 28 0
0 27 0
0 26 0
0 25 0
0 24 0
0 23 0
0 22 0
0 21 0
0 20 0
0 19 0
0 18 0
0 17 0
0 16 0
2 15 2
1 14 1
1 13 1
0 12 0
0 11 0
1 10 1
0 9 0
0 8 0
0 7 0
0 6 0
0 5 0
0 4 0
0 3 0
0 2 0
0 1 0

Answer Source

You need to use full outer join...

select
   coalesce(a.shoeorder, 0) as shoeorder
  ,coalesce(a.pdate, b.pdate) as pdate
  ,coalesce(b.pantorder, 0) as pantorder
from (
  SELECT COUNT(*) AS shoeorder, EXTRACT(DAY FROM tlDate) as pdate
  FROM tblshoes
  WHERE tlDate >= CURRENT_DATE - INTERVAL 1 MONTH
  GROUP BY EXTRACT(DAY FROM tlDate)
  ) a

left join (
  SELECT COUNT(*) AS pantorder, EXTRACT(DAY FROM tlDate) as pdate
  FROM tblpants
  WHERE tlDate >= CURRENT_DATE - INTERVAL 1 MONTH
  GROUP BY EXTRACT(DAY FROM tlDate)
  ) b
on a.pdate = b.pdate

union

select
   coalesce(a.shoeorder, 0) as shoeorder
  ,coalesce(a.pdate, b.pdate) as pdate
  ,coalesce(b.pantorder, 0) as pantorder
from (
  SELECT COUNT(*) AS shoeorder, EXTRACT(DAY FROM tlDate) as pdate
  FROM tblshoes
  WHERE tlDate >= CURRENT_DATE - INTERVAL 1 MONTH
  GROUP BY EXTRACT(DAY FROM tlDate)
  ) a

right join (
  SELECT COUNT(*) AS pantorder, EXTRACT(DAY FROM tlDate) as pdate
  FROM tblpants
  WHERE tlDate >= CURRENT_DATE - INTERVAL 1 MONTH
  GROUP BY EXTRACT(DAY FROM tlDate)
  ) b
on a.pdate = b.pdate
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download