SUM in mysql query is ambiguous in JOIN 2 tables

Question

There is something wrong with the SUM i made in the query as below:

EDIT I post my query in English so everyone understand what i try to say:

    $sql = "SELECT 
       c.stock,
       c.id as cid,
       cb.course as ccourse,
       cb.price_member as cbprice_member,
       cb.price_not_member as cbprice_not_member,
       cb.study as cbstudy,
       cb.studentid,


          (SELECT 
              SUM(CASE WHEN c.stock > 0 THEN price_member ELSE 0 END) AS subtotal_member,
              c.stock 
           FROM 
              courses_orders cb
           JOIN 
              courses c 
           ON 
              cb.course_id = c.id 
           WHERE 
              cb.date_removed IS NULL AND 
              cb.date_order_mail IS NOT NULL AND
              cb.date_pickup IS NULL AND 
              cb.date_pickup_mail IS NULL AND 
              cb.studentid = '$studentid' AND 
              cb.course_id = '$cid' AND 
              c.stock > 0
          ) as subtotal_member,


       (SELECT SUM(price_not_member) FROM courses_orders WHERE date_removed IS NULL AND date_order_mail IS NOT NULL AND date_pickup IS NULL AND date_pickup_mail IS NULL AND studentid = '$studentid' AND course_id = '$cid') as subtotal_not_member
     FROM 
       courses c
     JOIN
       courses_orders cb
     ON
       cb.course_id = c.id
     WHERE 
       c.id = '$cid' AND cb.date_removed IS NULL AND cb.date_pickup IS NOT NULL AND cb.date_pickup_mail IS NULL AND cb.studentid = '$studentid'
     ";

So, The problem is the SUM(CASE WHEN c.stock > 0 THEN price_member ELSE 0 END) AS subtotal_member and i have subtotal_member twice. I think that is also not correct. If i set cb.price_member i have the error: operand should contain 1 column and if i set price_member (without cb. before) i have the error: price_member is to ambiguous. What i want to do here is to have the total price of all items without the ones where the stock is below 1. So i take here c.voorraad > 0 but the result is always the totalprice with all items and not only the ones who has a stock above 0.

This is the original query (with Dutch items), it is the same as above query with different fields. So ignore this if you have read the EN version.

         $sql = "SELECT 
               c.voorraad,
               c.id as cid,
               cb.artikel as cbartikel,
               cb.prijs_lid as cbprijs_lid,
               cb.prijs_niet_lid as cbprijs_niet_lid,
               cb.studierichting as cbstudierichting,
               cb.studentid,


                  (SELECT 
                      SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid,
                      c.voorraad 
                   FROM 
                      cursusdienst_bestellingen cb
                   JOIN 
                      cursusdienst c 
                   ON 
                      cb.cursus_id = c.id 
                   WHERE 
                      datum_verwijderd IS NULL AND 
                      datum_reservatie_mail IS NOT NULL AND
                      datum_afhaling IS NULL AND 
                      datum_afhaling_mail IS NULL AND 
                      studentid = '$studentid' AND 
                      cursus_id = '$cid' AND 
                      c.voorraad > 0
                  ) as subtotaal_lid,


               (SELECT SUM(prijs_niet_lid) FROM cursusdienst_bestellingen WHERE datum_verwijderd IS NULL AND datum_reservatie_mail IS NOT NULL AND datum_afhaling IS NULL AND datum_afhaling_mail IS NULL AND studentid = '$studentid' AND cursus_id = '$cid') as subtotaal_niet_lid
             FROM 
               cursusdienst c
             JOIN
               cursusdienst_bestellingen cb
             ON
               cb.cursus_id = c.id
             WHERE 
               c.id = '$cid' AND cb.datum_verwijderd IS NULL AND cb.datum_afhaling IS NOT NULL AND cb.datum_afhaling_mail IS NULL AND cb.studentid = '$studentid'
             ";

The problem is the SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid and i have subtotaal_lid twice. I think that is also not correct. If i set cb.prijs_lid i have the error: operand should contain 1 column and if i set prijs_lid (without cb. before) i have the error: prijs_lid is to ambiguous.

What i want to do here is to have the total price of all items without the ones where the stock is below 1. So i take here c.voorraad > 0 but the result is always the totalprice with all items and not only the ones who has a stock above 0.

EDIT The table cursusdienst contains the following fields (e.g.):

id   prijs_lid   prijs_niet_lid   artikel  voorraad
1    24.00       25.00            Course1  12
2    30.00       35.00            Course2  -10

The table cursusdienst_bestellingen contains the following fields (e.g.):

id   cursus_id   prijs_lid   prijs_niet_lid   artikel  studentid
1    1           24.00       25.00            Course1  123456789
2    2           30.00       35.00            Course2  123456789

The output in a table (invoice) - main query who gives me the correct output:

Artikel   Aantal   Prijs
Course1   1        24.00

And below the output table the totalprice table - subquery were it goes wrong:

Subtotal: 24.00
Tax: 1.20
Total: 25.20

So the SUM totalprice_member (=totaalprijs_lid in dutch) here would be 24.00 for the members (lid in dutch) and not 54.00 for the members because the stock of course2 is below 1. Now i have the 54.00 for the totaalprijs_lid what is wrong...

The total code:

        <table cellpadding="0" cellspacing="0" width="600" class="w320">
            <tr>
              <td class="item-table">
                <table cellspacing="0" cellpadding="0" width="100%">
                  <tr>
                    <td class="title-dark" width="300">
                      Cursus
                    </td>
                    <td class="title-dark" width="163">
                      Aantal
                    </td>
                    <td class="title-dark" width="97">
                      Totaal
                    </td>
                  </tr>

<?php
    if (!empty($_POST['bachelor1'])) {
    foreach ($cursus as $cid) {

         $sql = "
select 
  c.voorraad,
  c.id as cid,
  cb.artikel as cbartikel,
  cb.prijs_lid as cbprijs_lid,
  cb.prijs_niet_lid as cbprijs_niet_lid,
  cb.studierichting as cbstudierichting,
  cb.studentid,
  case when c.voorraad > 0 then
    (
      select 
        sum(prijs_lid)
       from cursusdienst_bestellingen cbx
       where cbx.cursus_id = cb.cursus_id
         and cbx.studentid = cb.studentid
         and cbx.datum_afhaling is null
         and cbx.datum_afhaling_mail is null
         and cbx.datum_reservatie_mail is not null
         and cbx.datum_verwijderd is null
    )
  else 0 end as subtotaal_lid,
  case when c.voorraad > 0 then
    (
      select 
        sum(prijs_niet_lid)
       from cursusdienst_bestellingen cbx
       where cbx.cursus_id = cb.cursus_id
         and cbx.studentid = cb.studentid
         and cbx.datum_afhaling is null
         and cbx.datum_afhaling_mail is null
         and cbx.datum_reservatie_mail is not null
         and cbx.datum_verwijderd is null
    )
  else 0 end as subtotaal_niet_lid
from cursusdienst c
join cursusdienst_bestellingen cb on cb.cursus_id = c.id
where cb.datum_afhaling is not null 
  and cb.datum_afhaling_mail is null 
  and cb.datum_verwijderd is null 
  and cb.studentid = '$studentid'
  and c.id = '$cid'
                 ";
         $res = mysql_query($sql) or die (mysql_error());

         //$subtotaal1 = '';
         //$totaal1 = '';
         //$btw1 = '';

         while($row = mysql_fetch_assoc($res))
            { 
               $cursus_id1 = $row['cid'];
               $studierichting1 = $row['cbstudierichting'];
               $voorraad1 = $row['voorraad'];

               if ($num_rows_lid > 0) {
                   $prijs1 = round(number_format(($row['cbprijs_lid'] / 1.21), 2, '.', ''), 2);
               } else {
                   $prijs1 = round(number_format(($row['cbprijs_niet_lid'] / 1.21), 2, '.', ''), 2);
               }

               $artikel1 = $row['cbartikel'];
               $aantal1 = '1';

               $subtotaal_lid += number_format(round(($row['subtotaal_lid'] / 1.21), 2), 2, '.', '');
               $totaal_lid += number_format($row['subtotaal_lid'], 2, '.', '');
               $btw_lid = round(number_format(($totaal_lid - $subtotaal_lid), 2, '.', ''), 2);

               $subtotaal_niet_lid += number_format(round(($row['subtotaal_niet_lid'] / 1.21), 2), 2, '.', '');
               $totaal_niet_lid += number_format($row['subtotaal_niet_lid'], 2, '.', '');
               $btw_niet_lid = round(number_format(($totaal_niet_lid - $subtotaal_niet_lid), 2, '.', ''), 2);

?>
                  <tr>
                    <td class="item-col item">
                      <table cellspacing="0" cellpadding="0" width="100%">
                        <tr>
                          <td class="product">
                            <span style="color: #4d4d4d; font-weight:bold;"><?php echo wordwrap($artikel1, 20, "<br />\n"); ?></span>
                          </td>
                        </tr>
                      </table>
                    </td>
                    <td class="item-col quantity aantal">
                      <?php echo $aantal1; ?>
                    </td>
                    <td class="item-col">
                      <?php echo '€ '.($prijs1 * $aantal1); ?>
                    </td>
                  </tr>



<?php
         //$sql = "UPDATE cursusdienst_bestellingen SET datum_afhaling_mail = NOW() WHERE cursus_id = '$cursus_id1' AND datum_verwijderd IS NULL AND studentid = '$studentid'";
         //$res = mysql_query($sql) or die (mysql_error()); 

 } } } ?>


<!--
                  <tr>
                    <td class="item-col item">
                      <table cellspacing="0" cellpadding="0" width="100%">
                        <tr>
                          <td class="product">
                            <span style="color: #4d4d4d; font-weight: bold;">Pink Shoes</span> <br />
                            Newest styles
                          </td>
                        </tr>
                      </table>
                    </td>
                    <td class="item-col quantity aantal">
                      1
                    </td>
                    <td class="item-col price">
                      $10.50
                    </td>
                  </tr>
-->

                  <tr>
                    <td class="item-col item mobile-row-padding"></td>
                    <td class="item-col quantity"></td>
                    <td class="item-col price"></td>
                  </tr>

<?php
         if($num_rows_lid > 0) {
             $subtotaal = $subtotaal_lid;
             $btw = $btw_lid;
             $totaal = $totaal_lid;
         } else {
             $subtotaal = $subtotaal_niet_lid;
             $btw = $btw_niet_lid;
             $totaal = $totaal_niet_lid;
         }
?>
                  <tr>
                    <td class="item-col item">
                    </td>
                    <td class="item-col quantity" style="text-align:right; padding-right: 10px; border-top: 1px solid #cccccc;">
                      <span class="total-space">Subtotaal</span> <br />
                      <span class="total-space">BTW</span>  <br />
                      <span class="total-space" style="font-weight: bold; color: #4d4d4d">Totaal</span>
                    </td>
                    <td class="item-col price" style="text-align: left; border-top: 1px solid #cccccc;">
                      <span class="total-space"><?php echo '€ '.$subtotaal; ?></span> <br />
                      <span class="total-space"><?php echo '€ '.$btw; ?></span>  <br />
                      <span class="total-space" style="font-weight:bold; color: #4d4d4d"><?php echo '€ '.$totaal; ?></span>
                    </td>
                </table>
              </td>
            </tr>
        </table>

It seems you don't know how to apply the subqueries. You are selecting from the same tables again, even giving them the same alias names and you are not relating them to your main query. For one row in your results (which is mainly a cursusdienst_bestellingen record), what sums do you actually want to show? What is a subtotal_member and a subtotaal_niet_lid? Are these two values supposed to be the same in every result row or do they depend on the cursusdienst_bestellingen shown? — Thorsten Kettner
– Thorsten Kettner, Commented Oct 11, 2016 at 8:09
Maybe you could show some sample data and expected results, so we see what the sums cover. — Thorsten Kettner
– Thorsten Kettner, Commented Oct 11, 2016 at 8:27
The only thing the SUM must do is when a user is a member, subtotaal_lid is showing me the correct totalprice without the items where the stock (= voorraad) is empty. — Brecht27
– Brecht27, Commented Oct 11, 2016 at 9:34
subtotal_member is the same as subtotaal_lid (in dutch). This must be the totalprice of the column prijs_lid where the stock is above 0. All other items in the list where the stock is below the 0 must be removed from the totalprice_lid. — Brecht27
– Brecht27, Commented Oct 11, 2016 at 9:37
So these two values will be the same in every result row, yes? It doesn't matter what cursusdienst_bestellingen you are showing, you want to show with it the totals over all cursusdienst_bestellingen, yes? — Thorsten Kettner
– Thorsten Kettner, Commented Oct 11, 2016 at 9:39

Gordon Linoff · Accepted Answer · 2016-10-07 11:01:42Z

1

This code returns an ambiguous column error:

SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid,

This code returns "operand should contain one value":

SUM(CASE WHEN c.voorraad > 0 THEN cb.prijs_lid ELSE 0 END) AS subtotaal_lid,

This version fixes the previous error. The operand error is elsewhere in the query. At least one problem is this subquery:

(SELECT SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid,
        c.voorraad 
  . . . 
 )

You are using a subquery in a context with a single value is expected. This requires a scalar subquery, which returns one column and at most one row. You probably intend:

(SELECT SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid
  . . . 
 )

answered Oct 7, 2016 at 11:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Brecht27 Over a year ago

I have the following error: Column prijs_lid in field list is ambiguous if i use (SELECT SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid . . . ) And what about the subtotaal_lid i have twice?

Brecht27 Over a year ago

I have edited my query in English so it's better to understand for everyone

Xenos · Accepted Answer · 2016-10-11 08:27:47Z

0

Ambiguous columns means you have two tables with the same column name: which one MySQL must use?

Prefix every column name with the table name like tableName.columnName. Use a table alias (FROM tableName AS aliasName and JOIN tableName AS aliasName) if tableName gets too long.

Always do so, since it will make your query more robust. Ie: if table A contains column a and table B contains column b, then you do not need a prefix for now. But I encourage you to always put this prefix since you may add a column b to table A later, and without prefixes, you'll break your code.

Put the table alias for each ? in there.

Btw, you probably can rewrite that query using INNER/LEFT JOIN (maybe with subtables) instead of nesting SELECT in the columns value (which is usually way slower and harder to read).

SELECT 
    c.voorraad AS ?,
    c.id AS cid,
    cb.artikel AS cbartikel,
    cb.prijs_lid AS cbprijs_lid,
    cb.prijs_niet_lid AS cbprijs_niet_lid,
    cb.studierichting AS cbstudierichting,
    cb.studentid AS ?,
    (
        SELECT
            SUM(CASE WHEN c.voorraad > 0 THEN ?.prijs_lid ELSE 0 END) AS subtotaal_lid,
            c.voorraad
        FROM cursusdienst_bestellingen AS cb
        LEFT JOIN cursusdienst AS c ON cb.cursus_id = c.id
        WHERE 
            ?.datum_verwijderd IS NULL
            AND ?.datum_reservatie_mail IS NOT NULL
            AND ?.datum_afhaling IS NULL
            AND ?.datum_afhaling_mail IS NULL
            AND ?.studentid = '$studentid'
            AND ?.cursus_id = '$cid'
            AND c.voorraad > 0
    ) AS subtotaal_lid,
    (
        SELECT
            SUM(?.prijs_niet_lid) AS ?
        FROM cursusdienst_bestellingen AS ?
        WHERE 
            ?.datum_verwijderd IS NULL 
            AND ?.datum_reservatie_mail IS NOT NULL
            AND ?.datum_afhaling IS NULL
            AND ?.datum_afhaling_mail IS NULL 
            AND ?.studentid = '$studentid' 
            AND ?.cursus_id = '$cid'
    ) AS subtotaal_niet_lid
FROM cursusdienst AS c
LEFT JOIN cursusdienst_bestellingen AS cb ON cb.cursus_id = c.id
WHERE
    c.id = '$cid' 
    AND cb.datum_verwijderd IS NULL 
    AND cb.datum_afhaling IS NOT NULL 
    AND cb.datum_afhaling_mail IS NULL 
    AND cb.studentid = '$studentid'

edited Oct 11, 2016 at 8:27

answered Oct 10, 2016 at 8:36

Xenos

3,5172 gold badges31 silver badges52 bronze badges

9 Comments

Brecht27 Over a year ago

Can you see the problem in my query here? I can't see it.

Xenos Over a year ago

Yes, I see THEN prijs_lid with no table alias, so there must be two columns of that name in the tables involved in your query. Prefix prijs_lid with the table you want to use value from. But like MySQL, I cannot tell you which table you have in mind.

Brecht27 Over a year ago

If i use cb.prijs_lid (what is must be) then i have ambiguous error. What is see is that i have subtotaal_lid twice so if i take the first item to subtotaal_lid1 and the second (at the end of the select) as subtotaal_lid the error is gone but the result is not good. What i want to do here is to have the total price of all items without the ones where the stock is below 1. So i take here c.voorraad > 0 but the result is always the totalprice with all items and not only the ones who has a stock above 0.

Xenos Over a year ago

There may be multiple ambiguous columns since you have a lot of colnames without their table prefix.

Brecht27 Over a year ago

I have solved that issue but the result is still the same. See my original post for update.

|

Thorsten Kettner · Accepted Answer · 2016-10-11 11:33:02Z

0

I still don't know exactly what you are after. Your sample data doesn't explain anything.

Anayway, you'd have to correlate the subqueries with the main query somehow. In your main query you select from c and cb. In your subquery you want a sum related to the record shown in your main query. That should be more or less:

select 
  c.voorraad,
  c.id as cid,
  cb.artikel as cbartikel,
  cb.prijs_lid as cbprijs_lid,
  cb.prijs_niet_lid as cbprijs_niet_lid,
  cb.studierichting as cbstudierichting,
  cb.studentid,
  case when c.voorraad > 0 then
    (
      select 
        sum(prijs_lid)
       from cursusdienst_bestellingen cbx
       where cbx.cursus_id = cb.cursus_id
         and cbx.studentid = cb.studentid
         and cbx.datum_afhaling is null
         and cbx.datum_afhaling_mail is null
         and cbx.datum_reservatie_mail is not null
         and cbx.datum_verwijderd is null
    )
  else 0 end as subtotaal_lid,
  case when c.voorraad > 0 then
    (
      select 
        sum(prijs_niet_lid)
       from cursusdienst_bestellingen cbx
       where cbx.cursus_id = cb.cursus_id
         and cbx.studentid = cb.studentid
         and cbx.datum_afhaling is null
         and cbx.datum_afhaling_mail is null
         and cbx.datum_reservatie_mail is not null
         and cbx.datum_verwijderd is null
    )
  else 0 end as subtotaal_niet_lid
from cursusdienst c
join cursusdienst_bestellingen cb on cb.cursus_id = c.id
where cb.datum_afhaling is not null 
  and cb.datum_afhaling_mail is null 
  and cb.datum_verwijderd is null 
  and cb.studentid = '$studentid'
  and c.id = '$cid'

I don't know how close this is to what you really want to get. But hopefully you got the idea. As the two subqueries select the same records, you should move this to the FROM clause:

select 
  c.voorraad,
  c.id as cid,
  cb.artikel as cbartikel,
  cb.prijs_lid as cbprijs_lid,
  cb.prijs_niet_lid as cbprijs_niet_lid,
  cb.studierichting as cbstudierichting,
  cb.studentid,
  case when c.voorraad > 0 then total.sum_prijs_lid else 0 end as subtotaal_lid,
  case when c.voorraad > 0 then total.sum_prijs_niet_lid else 0 end as subtotaal_niet_lid
from cursusdienst c
join cursusdienst_bestellingen cb on cb.cursus_id = c.id
join
(
  select 
    cursus_id, 
    studentid, 
    sum(prijs_lid) as sum_prijs_lid, 
    sum(prijs_niet_lid) as sum_prijs_niet_lid
   from cursusdienst_bestellingen
   where datum_afhaling is null
     and datum_afhaling_mail is null
     and datum_reservatie_mail is not null
     and datum_verwijderd is null
   group by cursus_id, studentid
) total on  total.cursus_id = cb.cursus_id
        and total.studentid = cb.studentid
where cb.datum_afhaling is not null 
  and cb.datum_afhaling_mail is null 
  and cb.datum_verwijderd is null 
  and cb.studentid = '$studentid'
  and c.id = '$cid'

answered Oct 11, 2016 at 11:33

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

8 Comments

Brecht27 Over a year ago

What i have now with your first posted query is that the subtotaal_lid = the totalprice of all records and not only the ones who has voorraad > 0. So it is the same result i had before.

Brecht27 Over a year ago

I have posted the total code (original post) with the tables inside (html) and the query so you can look of there is something wrong with the subtotal.

Thorsten Kettner Over a year ago

The result contains rows. Each row is one cb with its one associated c. voorraad is a column in c. So either the row shown has voorraad <= 0, then 0 is shown. Or it has voorraad > 0 then all cbs for this c are summed up. In that sum there can never be records with voorraad <= 0, because they don't have a column voorraad; they belong to the c that has voorraad > 0. You are getting confused somehow and I don't know where.

Brecht27 Over a year ago

I my output i see that the totalprice is the total of all selected rows (also the ones with voorraad <= 0)... I don't know why... The first table with the course items (artikel - aantal - prijs) are correct. I have posted the whole output code with queries inside. Is there something wrong?

Thorsten Kettner Over a year ago

It should not be the total of the selected rows, because you select rows where datum_afhaling is not null whatever datum_reservatie_mail, but you sum records where datum_afhaling is null and datum_reservatie_mail is not null. If you would look at the same dates, then yes, the sum would equal the total of the selected rows, because both contain records for the same cursus_id and studentid.

|

Collectives™ on Stack Overflow

SUM in mysql query is ambiguous in JOIN 2 tables

3 Answers 3

2 Comments

9 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

9 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related