Link to home
Start Free TrialLog in
Avatar of Jazzy 1012
Jazzy 1012

asked on

Grouping certain rows together

<?php 
error_reporting(E_ALL);
ini_set('display_errors' ,1);
require "connection.php";


$query= "SELECT * from vouchers WHERE parsing_date=CURRENT_DATE ";
$result = pg_query($conn,$query);


?>

<!DOCTYPE html>
<html>
<head>
<link href="//netdna.bootstrapcdn.com/bootstrap/3.0.3/css/bootstrap.min.css" rel="stylesheet">
<link href = "http://fonts.googleapis.com/css?family=Roboto:400">

<style>
.responstable {
  margin: 1em 0;
  width: 100%;
  overflow: hidden;
  background: #FFF;
  color: #024457;
  border-radius: 10px;
  border: 1px solid #167F92;
  word-wrap: break-word;
}
</style>

</head>

<body>
<div class="container-fluid">
        <div>
        
          <h1>Clients</h1>
   
          
        </div>
        
<table class="responstable" rules='all' style='border-collapse: collapse;'>
<thead>
	<tr>
		<th>Client id</th>
		<th>Insurance</th>
		<th>Number of rows</th>
	 
	</tr>
</thead>
  <?php 
  	while($row = pg_fetch_array($result))
	{ 


  ?>
  
  <tbody>


     <td><?php echo $row['client_id']; ?></td>

     <td><?php echo $row['insurance']; ?></td> 
     
    
    
    </tr>
  <?php  }?>  </tbody>
</table>
 
</div>

</body>
</html>

Open in new window


I have the code above that outputs:

Client id           Insurance
------------           ---------------
123                    AA
123                    EE
123                         U
125                    AA
126                     CU
124                      UE
125                      UE
I want my out put to be:
Client id          Number of rows       Insurance
------------          -----------------------        --------------
123                               3                         AA,EE,U
125                               2                       AA,UE
126                                1                         CU
124                                1                          UE
Avatar of Jazzy 1012
Jazzy 1012

ASKER

I also tried this
SELECT 
  client_id, 
  concat(count(*), ' rows') AS rows, 
  array_agg(insurance) AS insurance 
FROM vouchers WHERE parsing_date=CURRENT_DATE
GROUP BY client_id;

Open in new window


But I got this error:
Warning: pg_query(): Query failed: ERROR: function concat(bigint, unknown) does not exist LINE 1: SELECT client_id, concat(count(*),'rows') AS rows, array_agg... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. in index.php on line 8

Open in new window

(line 8 Is that line(query))
ASKER CERTIFIED SOLUTION
Avatar of D Patel
D Patel
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Im using PostgreSQL not SQL
Okay but that still doesn't give me how I can put the number of rows.
add count(*) in your select statement
Where could I add that so my output would be in the row column?
Avatar of PortletPaul
this result
| Client id | string_agg |
|-----------|------------|
|       123 |    EE,U,AA |
|       124 |         UE |
|       125 |      AA,UE |
|       126 |         CU |
        

Open in new window

using the postgres function string_agg()
select "Client id", string_agg("Insurance",',')
from table1
group by "Client id"

Open in new window

data
CREATE TABLE Table1
    ("Client id" varchar(12), "Insurance" varchar(15))
;
    
INSERT INTO Table1
    ("Client id", "Insurance")
VALUES
    ('123', 'AA'),
    ('123', 'EE'),
    ('123', 'U'),
    ('125', 'AA'),
    ('126', 'CU'),
    ('124', 'UE'),
    ('125', 'UE')
;

Open in new window

see http://sqlfiddle.com/#!15/692bb/1

{+edit}
select "Client id", string_agg("Insurance",',' order by "Insurance")
from table1
group by "Client id"

Open in new window

to order the concatenation
string_agg doesnt work for me
?
what is the error message?
what version of Postgres are you using?
Im not sure Im using zend, but I did this code
array_agg(insurance) AS insurance

Open in new window

And the output is what I want but in them its like {"AA","UE"} or {"AA"}
Is there a way to remove the " or {}?
array_agg() isn't Postgres
I  can't help on PHP functions

do you want a Postres solution or a PHP solution?
did you try it like this?

$query= "
 SELECT v.Client_id, string_agg(v.Insurance,',' order by v.Insurance)
 from vouchers v
 WHERE v.parsing_date=CURRENT_DATE
 group by v.Client_id
";
$result = pg_query($conn,$query);
Didnt work but thanks anyways
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes my database is I just get the issue of it being unknown to my code.
Cant I just trim them because all i get when i change it is operation not exist
sorry: can't help much on the PHP errors
Its okay thank you