How to retrieve multiple columns count with different where clause?

Question

I need to construct the following table.

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg .tg-baqh{text-align:center;vertical-align:top}
.tg .tg-lap0{font-size:100%;text-align:center;vertical-align:top}
.tg .tg-yw4l{vertical-align:top}
</style>
<table class="tg" style="undefined;table-layout: fixed; width: 593px">
<colgroup>
<col style="width: 67px">
<col style="width: 72px">
<col style="width: 116px">
<col style="width: 116px">
<col style="width: 116px">
<col style="width: 106px">
</colgroup>
  <tr>
    <th class="tg-lap0">State</th>
    <th class="tg-baqh">City</th>
    <th class="tg-baqh">Three Days Ago</th>
    <th class="tg-baqh">Two Days Ago</th>
    <th class="tg-baqh">One Day Ago</th>
    <th class="tg-baqh">Total</th>
  </tr>
  <tr>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
  </tr>
  <tr>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
  </tr>
  <tr>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
  </tr>
  <tr>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
    <td class="tg-yw4l"></td>
  </tr>
</table>

Where state and city are simple selects, but three_days_ago, two_days_ago, one_day_ago are count of DB entries for the same city name. All this is in same table in DB.

So I image my query like something like this:

$union = pg_query($db, 
"
(
SELECT estado, municipio, COUNT(*)
FROM focos_bdq 
WHERE bioma LIKE 'Amazônia'
AND satelite LIKE 'AQUA_M-T' 
AND data_hora_gmt::date='$three_days_ago'
GROUP BY municipio, estado
ORDER BY COUNT(*) DESC 
)
UNION ALL
(
SELECT estado, municipio, COUNT(*)
FROM focos_bdq 
WHERE bioma LIKE 'Amazônia' 
AND data_hora_gmt::date='$two_days_ago'
GROUP BY municipio, estado
ORDER BY COUNT(*) DESC 
)
UNION ALL
(
SELECT estado, municipio, COUNT(*)
FROM focos_bdq 
WHERE bioma LIKE 'Amazônia' 
AND data_hora_gmt::date='$one_day_ago'
GROUP BY municipio, estado
ORDER BY COUNT(*) DESC 
)
"

But this query is resulting in one very long table, when I need to put dates in same row.

Edit your question and provide sample data and desired results *in a text format* so it is readable. — Gordon Linoff, Mar 07 '17 at 12:12
Guys, thanks for attention. It was resolved by post-processing in PHP... — Igor Kolesnikov, Mar 17 '17 at 12:24

score 0 · Answer 1 · edited Mar 09 '17 at 15:35

hard to tell without actual data, something like this should do the trick:

with 
3d as (
SELECT estado, municipio, COUNT(*)
FROM focos_bdq 
WHERE bioma LIKE 'Amazônia'
AND satelite LIKE 'AQUA_M-T' 
AND data_hora_gmt::date='$three_days_ago'
GROUP BY municipio, estado
ORDER BY COUNT(*) DESC 
)
, 2d as (
SELECT estado, municipio, COUNT(*)
FROM focos_bdq 
WHERE bioma LIKE 'Amazônia' 
AND data_hora_gmt::date='$two_days_ago'
GROUP BY municipio, estado
ORDER BY COUNT(*) DESC 
)
, 1d as (
SELECT estado, municipio, COUNT(*)
FROM focos_bdq 
WHERE bioma LIKE 'Amazônia' 
AND data_hora_gmt::date='$one_day_ago'
GROUP BY municipio, estado
ORDER BY COUNT(*) DESC 
)
select estado, municipio, d3.count,d2.count,d1.count,d3.count+d2.count+d1.count
from d3
left outer join d2 on d3.estado = d2.estado and d3.municipio = d2.municipio
left outer join d1 on d1.estado = d2.estado and d1.municipio = d2.municipio

How to retrieve multiple columns count with different where clause?

1 Answers1