Geek Answers Handbook

MySQL: returns 0 if the row does not exist

I stumbled on this a bit, so now I'm here :) I'm a beginner of SQL, so maybe this will be easy for you guys ...

I have this query:

SELECT COUNT(*) AS counter, recur,subscribe_date FROM paypal_subscriptions WHERE recur='monthly' and subscribe_date > "2010-07-16" and subscribe_date < "2010-07-23" GROUP BY subscribe_date ORDER BY subscribe_date

Now the dates that I showed above are hardcoded, my application will provide a variable date range.

Now I get a table of results where there is a value for this date.

 counter |recur | subscribe_date 2 | Monthly | 2010-07-18 3 | Monthly | 2010-07-19 4 | Monthly | 2010-07-20 6 | Monthly | 2010-07-22

I would like to return to the counter column if the date does not exist.

 counter |recur | subscribe_date 0 | Monthly | 2010-07-16 0 | Monthly | 2010-07-17 2 | Monthly | 2010-07-18 3 | Monthly | 2010-07-19 4 | Monthly | 2010-07-20 0 | Monthly | 2010-07-21 6 | Monthly | 2010-07-22 0 | Monthly | 2010-07-23

Is it possible?

+4
source share
3 answers

You will need a date table to group against. This is pretty easy in MSSQL using CTE, for example this - I'm not sure if MySQL has something like this? Otherwise, you will need to create a hard table as one exercise

EDIT: Try:

 SELECT COUNT(pp.subscribe_date) AS counter, dl.date, MIN(pp.recur) FROM date_lookup dl LEFT OUTER JOIN paypal pp on (pp.subscribe_date = dl.date AND pp.recur ='monthly') WHERE dl.date >= '2010-07-16' and dl.date <= '2010-07-23' GROUP BY dl.date ORDER BY dl.date
  • The subject of the request should be changed to the date_lookup table (the order of the left external connection becomes important)
  • Count (*) will not work, because the "date" record always exists - you need to read something in the PayPay table
  • pp.recur = 'month' is now a join condition, not a filter due to LOJ

Finally, showing pp.recur in the select list will not work.

I used aggregate, but MIN (pp.recur) will return null if there are no PayPal entries

What can you do when you parameterize your query, just repeat the filter like Recur? Again, plz justifies the MSSQL syntax

 SELECT COUNT(pp.subscribe_date) AS counter, dl.date, @ppRecur FROM date_lookup dl LEFT OUTER JOIN paypal pp on (pp.subscribe_date = dl.date AND pp.recur =@ppRecur ) WHERE dl.date >= @DateFrom and dl.date <= @DateTo GROUP BY dl.date ORDER BY dl.date
0
source

You will need a date table (a new table has been added), and then you will need to perform an outer join between this table and your query.

This question is also similar to another question. The answers can be very similar.

Insert dates in response to a query where it does not exist

0
source

Since there was no easy way for this, I had to fill in the blanks for me, and not return the data I wanted. I get a performance hit for this, but it was necessary to complete the report.

I will definitely get back what I want from the database in the near future. I will give a nonnb solution a try.

Thanks everyone!

0
source

More articles:


All Articles