Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Grouping certain rows together

Posted on 2016-09-15
18
75 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 7

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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 7

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
 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

856 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