On Mon, Jul 6, 2015 at 4:40 PM, Robert DiFalco <robert.difa...@gmail.com> wrote:
> I am fairly certain this does not give you the correct results. >> Specifically, the minimum value for each cDate is going to be 1 since >> count(*) counts NULLs. count(u) should probably work. >> >> >> Yes you are right, I forgot to change COUNT(*) to COUNT(id), as you > mention COUNT(u.*) will also work. I just couldn't get the idea of > generating a sequence form 0 to 6 to work correctly. The approach I'm using > seems to give the correct results (with COUNT(u.id)). > Correct. generate_series(0,6) won't work since there is no context as whether it is supposed to cover a single week or multiple years or anything in between. Any non-null column can be supplied to the count() function: count ignores nulls. In this case you want to ignore the placeholder null that you are creating during the left join. My original suggestion avoided these extra placeholder values and instead forces you to process the master date range and the user-by-date pieces separately and then substitute 0 for any master date where the corresponding user-by-date was missing. If performance were important it may be worth testing both versions otherwise my guess is this version is more readable (for you). David J.
