Solved

Grouping certain rows together

Posted on 2016-09-15
18
58 Views
Last Modified: 2016-09-15
<?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
0
Comment
Question by:Jasmine Ikhreishi
  • 10
  • 6
  • 2
18 Comments
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799599
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))
0
 
LVL 5

Accepted Solution

by:
D Patel earned 250 total points
ID: 41799601
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799605
Im using PostgreSQL not SQL
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799615
Okay but that still doesn't give me how I can put the number of rows.
0
 
LVL 5

Expert Comment

by:D Patel
ID: 41799633
add count(*) in your select statement
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799634
Where could I add that so my output would be in the row column?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41799638
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
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799642
string_agg doesnt work for me
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41799648
?
what is the error message?
what version of Postgres are you using?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799654
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 {}?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41799657
array_agg() isn't Postgres
I  can't help on PHP functions

do you want a Postres solution or a PHP solution?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41799664
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);
1
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799670
Didnt work but thanks anyways
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 41799676
you could be asked to define "didn't work" (it does not help isolate the problem)
are you sure your database is Postgres?

Could it be MySQL perhaps?
select `Client id`, group_concat(`Insurance`)
from table1
group by `Client id`

Open in new window

Every database vendor has their own unique way of doing the concatenation
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799678
Yes my database is I just get the issue of it being unknown to my code.
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799679
Cant I just trim them because all i get when i change it is operation not exist
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41799682
sorry: can't help much on the PHP errors
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799684
Its okay thank you
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now