count with join statement...

I have two tables as follows:
id               Date
1001      11/1/2014
1002      12/1/2014
1003      11/12/2014
1004      11/28/2014
1005      11/14/2014
1006      11/29/2014
1007      11/14/2014
1008      12/10/2014
1009      12/12/2014
1010      12/12/2014
1011      12/8/2014

id             status
1001      Started
1002      Started
1003      Started
1004      In Progress
1005      Complete
1006      Complete
1007      Complete
1007      Approved
1008      Approved
1009      Approved
1010      Complete
1011      Complete

I need to get an output that will be give me the count status depending on the date from table-01.

For eg, If I need the count of status for date >= 12/12/2014, I am looking for an output as follows:

Approve  1
Completed 1
Started  null
in Progress null

Can someone let me know how this can be done, thank you.

Who is Participating?
Robert ShermanConnect With a Mentor OwnerCommented:
So sorry, I was being called away and posted that in haste, I forgot this was a MySQL question...  Here is an updated version that follows the naming used in your example and should run under MySQL:  

SELECT Table-02.status, Count(Table-02.status) AS CountOfstatus
FROM Table-01 INNER JOIN Table-02 ON =
WHERE (((>='2014-12-12'))
GROUP BY Table02-status;

Open in new window

Robert ShermanOwnerCommented:
This will get you the ones that have values... it doesn't return the ones that are null.

SELECT Table2.status, Count(Table2.status) AS CountOfstatus
FROM Table1 INNER JOIN Table2 ON =
WHERE (((Table1.when)>=#12/12/2014#))
GROUP BY Table2.status;

Open in new window

aej1973Author Commented:
Is there a ".when" clause in MySQL also, what is this # symbol? I tried the following statement but it did not work:

SELECT table2.status, Count(table2.status) AS CountOfstatus
FROM table1 INNER JOIN table2 ON =
WHERE ((table1.when)>=#12/12/2014#)
GROUP BY table2.status

The error is :
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY table2.status LIMIT 0, 30' at line 4
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Robert ShermanOwnerCommented:
Also, if you need a query that returns null for those values in status that have no records within the date range you specify, it gets a little more complicated.  

Do you have the values for status stored in a separate table?  (one with four rows containing 'Approved' 'Started' 'In Progress' 'Complete')   If you do, you could use a left join from that table to a sub-query containing the query I posted above.   For example, if the lookup table is called 'status_tbl':

Select a.status, b.CountOfstatus from status_tbl as a LEFT JOIN 
       SELECT Table2.status, Count(Table2.status) AS CountOfstatus
           FROM Table1 INNER JOIN Table2 ON =
           WHERE (((Table1.when)>=#12/12/2014#))
           GROUP BY Table2.status;
      )  AS b ON a.status = b.status;

Open in new window

aej1973Author Commented:
Sherman, looks like for the second query you have not done it with MySQL :-). Thank you very much for your help. I am taking a look at this.

aej1973Author Commented:
Sherman, thank you. I was able to figure it out with the guidance you gave me.  Thanks again for the help.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.