Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Grouping certain rows together

Posted on 2016-09-15
18
Medium Priority
?
112 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
[X]
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
  • 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 1000 total points
ID: 41799601
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799605
Im using PostgreSQL not SQL
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 49

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 49

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 49

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 49

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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1000 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 49

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 Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

609 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