+- Hack Forums (https://hackforums.net)
+-- Forum: Tech (https://hackforums.net/forumdisplay.php?fid=444)
+--- Forum: Coding (https://hackforums.net/forumdisplay.php?fid=151)
+---- Forum: Coders Lounge (https://hackforums.net/forumdisplay.php?fid=5)
+---- Thread: [DBMS] Select the from the calendar and but only select the right uid (/showthread.php?tid=4182060)
Select the from the calendar and but only select the right uid - MarMigs - Apr 19, 2014
Hello,
I've been working with this little piece of code a while and it has not working still dont know why.
Basically I'm trying to get a full calendar and then put the total_profit in going to the days.
Code
SELECT calendar.date AS DATE,
IFNULL(SUM(sells.price),0.00) AS total_profit
FROM sells RIGHT JOIN calendar ON (DATE(sells.date) = calendar.date)
WHERE (calendar.date BETWEEN (CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE)) AND (LAST_DAY(NOW())))
GROUP BY DATE;This piece of code works, but when I try to refine the search and put an uid, like
Code
SELECT calendar.date AS DATE,
IFNULL(SUM(sells.price),0.00) AS total_profit
FROM sells RIGHT JOIN calendar ON (DATE(sells.date) = calendar.date)
WHERE (calendar.date BETWEEN (CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE)) AND (LAST_DAY(NOW()))) AND uid = 2
GROUP BY DATE;It doesn't work, just leaves the days that it is not null or not 0.00, how can I get the full calendar like the below and still refine the search to only match the uid I want?
Code
+------------+--------------+
| DATE | total_profit |
+------------+--------------+
| 2014-04-01 | 5.00 |
| 2014-04-02 | 0.00 |
| 2014-04-03 | 0.00 |
| 2014-04-04 | 0.00 |
| 2014-04-05 | 0.00 |
| 2014-04-06 | 0.00 |
| 2014-04-07 | 0.00 |
| 2014-04-08 | 0.00 |
| 2014-04-09 | 0.00 |
| 2014-04-10 | 0.00 |
| 2014-04-11 | 0.00 |
| 2014-04-12 | 0.00 |
| 2014-04-13 | 0.00 |
| 2014-04-14 | 0.00 |
| 2014-04-15 | 0.00 |
| 2014-04-16 | 0.00 |
| 2014-04-17 | 0.00 |
| 2014-04-18 | 0.00 |
| 2014-04-19 | 35.98 |
| 2014-04-20 | 0.00 |
| 2014-04-21 | 0.00 |
| 2014-04-22 | 0.00 |
| 2014-04-23 | 0.00 |
| 2014-04-24 | 0.00 |
| 2014-04-25 | 0.00 |
| 2014-04-26 | 0.00 |
| 2014-04-27 | 0.00 |
| 2014-04-28 | 0.00 |
| 2014-04-29 | 0.00 |
| 2014-04-30 | 0.00 |
+------------+--------------+Every help is appreciated.