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>
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Im using PostgreSQL not SQL
ASKER
Okay but that still doesn't give me how I can put the number of rows.
add count(*) in your select statement
ASKER
Where could I add that so my output would be in the row column?
this result
{+edit}
| Client id | string_agg |
|-----------|------------|
| 123 | EE,U,AA |
| 124 | UE |
| 125 | AA,UE |
| 126 | CU |
using the postgres function string_agg()
select "Client id", string_agg("Insurance",',')
from table1
group by "Client id"
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')
;
see http://sqlfiddle.com/#!15/692bb/1{+edit}
select "Client id", string_agg("Insurance",',' order by "Insurance")
from table1
group by "Client id"
to order the concatenation
ASKER
string_agg doesnt work for me
?
what is the error message?
what version of Postgres are you using?
what is the error message?
what version of Postgres are you using?
ASKER
Im not sure Im using zend, but I did this code
Is there a way to remove the " or {}?
array_agg(insurance) AS insurance
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?
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_DAT E
group by v.Client_id
";
$result = pg_query($conn,$query);
$query= "
SELECT v.Client_id, string_agg(v.Insurance,','
from vouchers v
WHERE v.parsing_date=CURRENT_DAT
group by v.Client_id
";
$result = pg_query($conn,$query);
ASKER
Didnt work but thanks anyways
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes my database is I just get the issue of it being unknown to my code.
ASKER
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
ASKER
Its okay thank you
ASKER
Open in new window
But I got this error:
Open in new window
(line 8 Is that line(query))