aej1973
asked on
count with join statement...
I have two tables as follows:
Table-01
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
Table-02
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.
A
Table-01
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
Table-02
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.
A
ASKER
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 table1.id = table2.id
WHERE ((table1.when)>=#12/12/201 4#)
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
SELECT table2.status, Count(table2.status) AS CountOfstatus
FROM table1 INNER JOIN table2 ON table1.id = table2.id
WHERE ((table1.when)>=#12/12/201
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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':
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 Table1.id = Table2.id
WHERE (((Table1.when)>=#12/12/2014#))
GROUP BY Table2.status;
) AS b ON a.status = b.status;
ASKER
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.
A
A
ASKER
Sherman, thank you. I was able to figure it out with the guidance you gave me. Thanks again for the help.
A
A
Open in new window