code_junkie

Postgres - how to return rows with 0 count for missing data?

Nov 12th, 2011
66
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.92 KB | None | 0 0
  1. select to_char(date_trunc('month',date), 'YYYY-MM-DD'),count(distinct post_id)
  2. from some_table where category_id=1 and entity_id = 77 and entity2_id = 115
  3. and date <= '2008-12-06' and date >= '2007-12-01' group by
  4. date_trunc('month',date) order by date_trunc('month',date);
  5. to_char | count
  6. ------------+-------
  7. 2007-12-01 | 64
  8. 2008-01-01 | 31
  9. 2008-02-01 | 14
  10. 2008-03-01 | 21
  11. 2008-04-01 | 28
  12. 2008-05-01 | 44
  13. 2008-06-01 | 100
  14. 2008-07-01 | 72
  15. 2008-08-01 | 91
  16. 2008-09-01 | 92
  17. 2008-10-01 | 79
  18. 2008-11-01 | 65
  19. (12 rows)
  20.  
  21. select to_char(date_trunc('month',date), 'YYYY-MM-DD'),count(distinct post_id)
  22. from some_table where category_id=1 and entity_id = 75 and entity2_id = 115
  23. and date <= '2008-12-06' and date >= '2007-12-01' group by
  24. date_trunc('month',date) order by date_trunc('month',date);
  25.  
  26. to_char | count
  27. ------------+-------
  28.  
  29. 2007-12-01 | 2
  30. 2008-01-01 | 2
  31. 2008-03-01 | 1
  32. 2008-04-01 | 2
  33. 2008-06-01 | 1
  34. 2008-08-01 | 3
  35. 2008-10-01 | 2
  36. (7 rows)
  37.  
  38. to_char | count
  39. ------------+-------
  40. 2007-12-01 | 2
  41. 2008-01-01 | 2
  42. 2008-02-01 | 0
  43. 2008-03-01 | 1
  44. 2008-04-01 | 2
  45. 2008-05-01 | 0
  46. 2008-06-01 | 1
  47. 2008-07-01 | 0
  48. 2008-08-01 | 3
  49. 2008-09-01 | 0
  50. 2008-10-01 | 2
  51. 2008-11-01 | 0
  52. (12 rows)
  53.  
  54. select distinct date_trunc('month', (current_date - offs)) as date
  55. from generate_series(0,365,28) as offs;
  56. date
  57. ------------------------
  58. 2007-12-01 00:00:00+01
  59. 2008-01-01 00:00:00+01
  60. 2008-02-01 00:00:00+01
  61. 2008-03-01 00:00:00+01
  62. 2008-04-01 00:00:00+02
  63. 2008-05-01 00:00:00+02
  64. 2008-06-01 00:00:00+02
  65. 2008-07-01 00:00:00+02
  66. 2008-08-01 00:00:00+02
  67. 2008-09-01 00:00:00+02
  68. 2008-10-01 00:00:00+02
  69. 2008-11-01 00:00:00+01
  70. 2008-12-01 00:00:00+01
Add Comment
Please, Sign In to add comment