Solved

Grouping certain rows together

Posted on 2016-09-15
18
90 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 250 total points
ID: 41799601
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799605
Im using PostgreSQL not SQL
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

734 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