Combining multiple MySQL queries into 1

Question

I am trying to display a summary table which is currently using multiple MySQL queries and I was wondering if it's possible to combine them into one.

I have a table called 'payments' with the fields amount and currency, and i'm trying to display a summary totals table for each of the currencies. As an example:

<table>
  <tr>
    <td>USD</td>
    <td>EUR</td>
    <td>GBP</td>
  </tr>
  <tr>
  <? $q1 = mysqli_query("SELECT sum(amount) as total_USD from payments WHERE currency='USD'");
  while($row1 = mysqli_fetch_assoc($q1)){ ?>                
    <td><? echo number_format($row1['total_USD'],0); ?></td>
  <? } ?>

  <? $q2 = mysqli_query("SELECT sum(amount) as total_EUR from payments WHERE currency='EUR'");
  while($row2 = mysqli_fetch_assoc($q2)){ ?>                
    <td><? echo number_format($row2['total_EUR'],0); ?></td>
  <? } ?>

  <? $q3 = mysqli_query("SELECT sum(amount) as total_GBP from payments WHERE currency='GBP'");
  while($row3 = mysqli_fetch_assoc($q3)){ ?>                
    <td><? echo number_format($row3['total_GBP'],0); ?></td>
  <? } ?>
  </tr>
</table>

In reality, I am using 12 currencies so I have 12 separate queries but it feels inefficient so I was wondering if there is an easier way to achieve the same result?

Many thanks!

Mark Baker · Accepted Answer · 2013-04-13 09:54:35Z

3

SELECT currency,
       SUM(amount) as total
  FROM payments
 GROUP BY currency

answered Apr 13, 2013 at 9:54

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eric Herlitz Over a year ago

This is known as aggregate functions, mysqltutorial.org/mysql-aggregate-functions.aspx

mckeegan375 Over a year ago

Thanks Mark! and everyone else for your help. Group by worked a treat :)

venkatesh · Accepted Answer · 2013-04-13 10:07:00Z

2

     Solution 1:
       SELECT currency, SUM(amount) as total FROM payments GROUP BY currency
     Solution 2 :

   $currencies=array(USD,EUR,GBP,...);
    foreach($currencies as $currency){
      $q1= mysqli_query("SELECT sum(amount) as total from payments WHERE currency='".$currency."'");  
     while($row1 = mysqli_fetch_assoc($q1)){                
        echo number_format($row1['total'],0); 
       } 
    }

answered Apr 13, 2013 at 10:07

venkatesh

1541 silver badge13 bronze badges

1 Comment

Mark Baker Over a year ago

In your loop, you're using MySQLi, so you can use a prepared statement with a currency bind variable: using prepared statements isn't just a protection against SQL injection, but can help performance as MySQL doesn't need to parse the statement each time it executes (just the first time), so it's more efficient to use bind vars as well as more secure

halfdan · Accepted Answer · 2013-04-13 09:55:20Z

1

You can use a GROUP BY here.

SELECT currency, SUM(amount) as total FROM payments GROUP BY currency

http://dev.mysql.com/doc/refman/5.1/en/group-by-modifiers.html

answered Apr 13, 2013 at 9:55

halfdan

34.3k8 gold badges82 silver badges88 bronze badges

Collectives™ on Stack Overflow

Combining multiple MySQL queries into 1

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related