Php and sql query

Question

Php and sql query

Navigation

#1 by (1 votes)

1

I have a problem with an sql query with php, I need to calculate the total of units sold by vendor, I am limiting the number of records to show because the database has hundreds of records, and I am just trying out the query.

But when making the query I get these two errors:

Warning: odbc_exec (): SQL error: [TOD] [ODBC] [GENESIS] Non aggregates require a GROUP BY expression.

Warning: odbc_fetch_row () expects parameter 1 to be resource, boolean given in

This is my code:

 <?php 
  $sqlVend = "SELECT TOP 10 VEN_LLAVE, VEN_NOMBRE, SUM(VDOC_UDS) AS Suma, 
              from VENVEN, VENDOC 
              where VENVEN.VEN_LLAVE = VENDOC.VDOC_VEND GROUP BY VEN_LLAVE, VEN_NOMBRE";
  $resVend = odbc_exec($cone, $sqlVend);
  while (odbc_fetch_row($resVend)) {
    # code...
?>
<tr>
  <td><?php echo odbc_result($resVend, 'VEN_LLAVE') ?></td>
  <td><?php echo odbc_result($resVend, 'VEN_NOMBRE') ?></td>
  <td><?php echo odbc_result($resVend, 'Suma') ?></td>

</tr>
<?php } ?>

sql php odbc

asked by Byte 19.07.2016 в 23:30

source

1 answer

Entity Framework map some properties Store large hours in python

score 1 · Accepted Answer

Groupby

The problem is that you have VDOC_UDS in the select but not in the aggregation clause.

You tell him to group by VEN_LLAVE and VEN_NOMBRE, and in the selection you have those columns and the sum of the units, until then everything is correct. You also ask for the units, but the units corresponding to what? For each pair (VEN_LLAVE, VEN_NOMBRE) there can be several values of VDOC_UDS and the database does not know what to give you.

A simple example:

+----------+------------+-------+ 
|VEN_LLAVE | VEN_NOMBRE |VEN_UDS|
+----------+------------+-------+ 
|    1     |   Luis     |   4   |
+----------+------------+-------+ 
|    1     |   Luis     |   12  |
+----------+------------+-------+ 
|     1    |   Luis     |   1   |
+----------+------------+-------+ 
|     2    |  Marcos    |   7   |
+----------+------------+-------+
|     2    |  Marcos    |   3   |
+----------+------------+-------+ 
|     2    |    Juan    |   13  |
+----------+------------+-------+

With your query if you did not return an error, the result would be:

+----------+------------+-------+------------+ 
|VEN_LLAVE | VEN_NOMBRE |VEN_UDS|sum(VEN_UDS)|
+----------+------------+-------+------------+
|    1     |    Luis    |   ??  |     17     |
+----------+------------+-------+------------+
|    2     |    Marcos  |   ??  |     10     | 
+----------+------------+-------+------------+
|    2     |    Juan    |   13  |     13     |
+----------+------------+-------+------------+

As you see for the first two rows the column VEN_UDS does not make sense, you should remove it from the query.

Top

Here it seems to me that you have a wrong idea of how top works, this function what limits is the number of rows that the database returns after having made all the appropriate calculations, so if the table is large this condition will not make the query faster.

If you want to limit the number of rows to which the sum is applied you have to do it through the clause where limiting the query to some ids or some determined days for example.