What is missing from my SQL sequence?

Question

What is missing from my SQL sequence?

Navigation

#1 by (1 votes)

0

You see, I have to list some basic data for a sales report by dates, the problem I have is the following, if a client in the invoice 2 buys 10 articles I list the values of each article and what I need is that I totalized the total value of the invoice regardless of the invoice detail.
An example of what I need would be something like this:

Numero de factura | nombre cliente | fecha_factura | total

FV - 1            | Pepito Perez   | 2016-02-05    | $10.000

FV - 2            | Pablo botija   | 2016-02-05    | $500.000(asi haya comprado mil artículos, que me los totalice de esta manera)

MY CODE

$sql = ejecutar("SELECT 
                    factura.numfactura
                    , factura.vendedor
                    , factura.fechfactura
                    , concat(cliente.nomclie,' ', cliente.apeclie) as nomcliente
                    , formapago.descripcion as nomformapago
                    , (detallefactura.cantarti * detallefactura.valorunidad) as total
                FROM 
                    factura 
                INNER JOIN 
                    cliente ON factura.idclie = cliente.idcliente
                INNER JOIN 
                    formapago ON factura.idformapago = formapago.idformapago
                LEFT JOIN 
                    detallefactura ON factura.idfactura = detallefactura.idfactura
                WHERE
                    factura.fechfactura BETWEEN '".$fechainicial."' AND '".$fechafinal."'",$cnx);

I would appreciate if you can help me.

mysql php phpmyadmin

asked by JDavid 12.02.2017 в 03:43

source

1 answer

Is there any way to create autoscroll to a listview ?, in Android Studio SyntaxError: unterminated string literal

score 1 · Accepted Answer

Maybe you can use this, use the function SUM and make a GROUP BY by the invoice number (the latter may be optional, so try without it and with it, I took the trouble to copy just the query and put an example date to make the code more readable):

SELECT 
    factura.numfactura
    , factura.vendedor
    , factura.fechfactura
    , concat(cliente.nomclie,' ', cliente.apeclie) as nomcliente
    , formapago.descripcion as nomformapago
    , SUM((detallefactura.cantarti * detallefactura.valorunidad)) as total
FROM 
    factura 
INNER JOIN 
    cliente ON factura.idclie = cliente.idcliente
INNER JOIN 
    formapago ON factura.idformapago = formapago.idformapago
LEFT JOIN 
    detallefactura ON factura.idfactura = detallefactura.idfactura
WHERE
    factura.fechfactura BETWEEN '2017-01-01' AND '2017-31-01'
GROUP BY
    factura.numfactura