For loop inside SQL query

Question

I have a table named watersourcetype consisting of water types.

and another table health_and_sanitation consisting of household no. and watersource_id I have this query :

SELECT h.purok_number,
    SUM(CASE WHEN hs.watersystem = 'Community water system-own' THEN 1 ELSE 0 END) AS a,
    SUM(CASE WHEN hs.watersystem = 'Community water system-shared' THEN 1 ELSE 0 END) AS b,
    SUM(CASE WHEN hs.watersystem = 'Deep well-own' THEN 1 ELSE 0 END) AS c,
    SUM(CASE WHEN hs.watersystem = 'Deep well-shared' THEN 1 ELSE 0 END) AS d,
    SUM(CASE WHEN hs.watersystem = 'Artesian well-own' THEN 1 ELSE 0 END) AS e,
    SUM(CASE WHEN hs.watersystem = 'Artesian well-shared' THEN 1 ELSE 0 END) AS f,
    SUM(CASE WHEN hs.watersystem = 'Dug/shallow well-own' THEN 1 ELSE 0 END) AS g,
    SUM(CASE WHEN hs.watersystem = 'Dug/shallow well-shared' THEN 1 ELSE 0 END) AS h,
    SUM(CASE WHEN hs.watersystem = 'River, stream, lake, spring, bodies of water' THEN 1 ELSE 0 END) AS i,
    SUM(CASE WHEN hs.watersystem = 'Bottled water' THEN 1 ELSE 0 END) AS j,
    SUM(CASE WHEN hs.watersystem = 'Tanker truck/Peddler' THEN 1 ELSE 0 END) AS k
FROM health_and_sanitation AS hs, house_hold AS h, f_member as f 
WHERE
     h.brgy_name='$brgy_name' AND 
     h.hh_number=hs.hh_number AND 
     h.hh_number=f.hh_number AND
     f.is_household='HOUSEHOLD' AND 
     EXTRACT(YEAR FROM f.reg_date) BETWEEN '$sel_year' AND '$sel_year' 
group by h.purok_number 
order by h.purok_number

what i want is to put a for loop inside above sql query since table watersourcetype is dynamic another data will be added to watersourcetype soon so i dont have to define in my case statement on above query the watersystem . The query should look like this :

$qry = pg_query("select cwatertype from tbl_watersourcetype");

SQL:

SELECT h.purok_number, 
           // is this possible ? putting a while loop or forloop inside a query in PHP ?
          while($row = pg_fetch_array($qry)) 
          {
           SUM(CASE WHEN hs.watersystem = '$row['cwatertype']' THEN 1 ELSE 0 END) AS a 
          }
FROM health_and_sanitation AS hs, house_hold AS h, f_member as f 
WHERE
    h.brgy_name='$brgy_name' AND 
    h.hh_number=hs.hh_number AND 
    h.hh_number=f.hh_number AND
    f.is_household='HOUSEHOLD' AND 
    EXTRACT(YEAR FROM f.reg_date) BETWEEN '$sel_year' AND '$sel_year' 
group by h.purok_number 
order by h.purok_number

is that possible ?

don't build your own pivot tables... http://stackoverflow.com/questions/20618323/create-a-pivot-table-with-postgres-sql — Marc B, Mar 12 '14 at 03:55
Post the all the tables' structures and sample data. `\d health_and_sanitation` — Clodoaldo Neto, Mar 12 '14 at 12:17
is it possible to put while statment inside a query if it is, then how ? $qry = pg_query("select cwatertype from tbl_watersourcetype"); $sql = "SELECT h.purok_number, "; while($row = pg_fetch_array($qry)) { SUM(CASE WHEN hs.watersystem = '$row['cwatertype']' THEN 1 ELSE 0 END) AS a } — daR, Mar 13 '14 at 08:15

score 2 · Answer 1 · answered Mar 12 '14 at 11:12

If I understand your query correctly, you are trying to retrieve the number of watersourcetypes per household, with a single record returned per household. If that is indeed the case you need to use the crosstab() function from the tablefunc extension.

However, if you can get by with multiple rows giving the number of sources per watersourcetype per household then you can simply and more efficiently use the COUNT() aggregate in the SELECT statement (with some decent formatting for legibility):

SELECT h.purok_number, hs.watersystem, COUNT(hs.watersystem) AS num
   FROM health_and_sanitation AS hs,
        house_hold AS h,
        f_member AS f
   WHERE h.brgy_name='$brgy_name'
     AND h.hh_number=hs.hh_number
     AND h.hh_number=f.hh_number
     AND f.is_household='HOUSEHOLD'
     AND EXTRACT(YEAR FROM f.reg_date) BETWEEN '$sel_year' AND '$sel_year'
   GROUP BY h.purok_number, hs.watersystem
   ORDER BY h.purok_number;

Additionally, I am assuming that your health_and_sanitation table is a view on the watersourcetype table; otherwise you are duplicating data in two tables which is (generally) a big boo-boo. Given also your issue with additional water source type, you may want to see if your database design is in 3NF.

Cheers, Patrick

not what I want sir, what i want is to put a loop inside a select query statement based on another table value. But thanks for the answer by the way :) — daR, Mar 13 '14 at 05:11
@daR: I beg to differ about what you want. You want to solve a query problem. You _think_ you need a loop inside a query statement, but you can't have it. It doesn't exist. So find another solution. If you insist that you want a loop inside a select query then I suggest you hack the source code to make it so ;-) The only thing that would approximate what you want is to use the EXECUTE command inside of a function to parse together a very ugly and inefficient query to do something that could be done in other ways with far less effort and at higher speed and efficiency. — Patrick, Mar 13 '14 at 12:27

score 1 · Accepted Answer · answered Mar 17 '14 at 03:50

this is an example on how to put for loop inside SELECT statement :

$qry_6_12 .= "  SELECT count(ncropfarmingreasonid) as counted , " ;

     for($i=2;$i<=$count_row;$i++) // loop the number of rows and used $i as ncropfarmingreasonid 
          {           
            if(($count_row-$i)==0)
              {
                 $qry_6_12 .= "SUM(CASE WHEN ncropfarmingreasonid = ".$i." THEN 1
                 ELSE 0 END) a".$i."";
              }  
            else 
              {
                 $qry_6_12 .= "SUM(CASE WHEN ncropfarmingreasonid = ".$i." THEN 1  
                 ELSE 0 END) a".$i.",";                                  
              }        
          }
      $qry_6_12 .= " FROM tbl_climatechange as c, tbl_household as h, tbl_barangay as b where h.chholdnumber=c.chholdnumber and b.cbrgycode=h.cbrgycode and b.cbrgyname = 'AMPAYON' ";
      $query_6_12 = pg_query($qry_6_12);

For loop inside SQL query

2 Answers2