I have a table of transactions for ArticleID bought and sold.
I want to create a report of the fileds in that table sorted by the SUM of each ArticleID.
Any suggestions???
Thanks
Richard
In answer to my own quetion I came up with:
select sum( CantidadPedida ) as SumPedida, sum( CantidadRecibida ) as SumRecibida, ArticuloCodigo, ArticuloNombre, ArticuloCosto, ArticulosDisponibles, ArticuloUnidad from ArticulosPedidos inner join PedidosIndex on PedidosIndex.NumDePedido = ArticulosPedidos.NumDePedido inner join CatArticulos on ArticulosPedidos.ArticuloID = CatArticulos.ArticuloID where ArticulosPedidos.Fecha between '{$_POST{'MovsDelAno'}}-{$_POST{'MovsDelMes'}}-{$_POST{'MovsDelDia'}}' and '{$_POST{'MovsAlAno'}}-{$_POST{'MovsAlMes'}}-{$_POST{'MovsAlDia'}}' group by ArticulosPedidos.ArticuloID order by SumPedida DESC limit 500"
Any suggestions or criticisms are most appreciated
Thanks
Richard
On 07/30/2013 04:08 PM, Richard Couture wrote:
I have a table of transactions for ArticleID bought and sold.
I want to create a report of the fileds in that table sorted by the SUM of each ArticleID.
Any suggestions???
Thanks
Richard
well here is not php, but... it's a criticism... before this query, did you checked that $_POST[*] are double or int values? if they are string, maybe you can have sql injection in your app... well if it's php here is one example what i'm talking about...
$_POST['MovsDelAno']=(int)$_POST['MovsDelAno']; $_POST['MovsDelMes']=(int)$_POST['MovsDelMes']; $_POST['MovsDelDia']=(int)$_POST['MovsDelDia']; $_POST['MovsAlAno']=(int)$_POST['MovsAlAno']; $_POST['MovsAlMes']=(int)$_POST['MovsAlMes']; $_POST['MovsAlDia']=(int)$_POST['MovsAlDia'];
// now concat your strings... $SQL="select sum( CantidadPedida ) as SumPedida, sum( CantidadRecibida ) as SumRecibida, ArticuloCodigo, ArticuloNombre, ArticuloCosto, ArticulosDisponibles, ArticuloUnidad from ArticulosPedidos inner join PedidosIndex on PedidosIndex.NumDePedido = ArticulosPedidos.NumDePedido inner join CatArticulos on ArticulosPedidos.ArticuloID = CatArticulos.ArticuloID where ArticulosPedidos.Fecha between '{$_POST['MovsDelAno']}-{$_**POST['MovsDelMes']}-{$_POST['**MovsDelDia']}' and '{$_POST['MovsAlAno']}-{$_**POST['MovsAlMes']}-{$_POST['**MovsAlDia']}' group by ArticulosPedidos.ArticuloID order by SumPedida DESC limit 500"
about what this query do, i don't know... but from mysq
ops, i pressed enter without full message, sorry
from mysql 4.1 (when sub query started) i "ported" my queries to something like: SELECT XXXX FROM table1,table2 WHERE (joins go here) AND (filters go here) GROUP BY fields ORDER BY fields LIMIT xxxx OFFSET xxxx
example..
SELECT SUM( CantidadPedida ) AS SumPedida, SUM( CantidadRecibida ) AS SumRecibida, ArticuloCodigo, ArticuloNombre, ArticuloCosto, ArticulosDisponibles, ArticuloUnidad
FROM ArticulosPedidos, PedidosIndex,CatArticulos WHERE PedidosIndex.NumDePedido = ArticulosPedidos.NumDePedido AND ArticulosPedidos.ArticuloID = CatArticulos.ArticuloID AND
ArticulosPedidos.Fecha BETWEEN '{$_POST['MovsDelAno']}-{$_**POS T['MovsDelMes']}-{$_POST['**MovsDelDia']}' AND '{$_POST['MovsAlAno']}-{$_** POST['MovsAlMes']}-{$_POST['**MovsAlDia']}'
GROPU BY ArticulosPedidos.ArticuloID ORDER BY SumPedida DESC LIMIT 500
---- other tip, instead of full table name, *USE* alias FROM ArticulosPedidos AS a, PedidosIndex AS b, CartArticulos AS c b.NumDePedido=a.NumDePedido AND a.ArticuloID=c.ArticuloID
this decrease TCP/IP or UNIX SOCKET packets... if you have a very high qps (query per second) this can save a lot of ethernet, example 1000 queries of 1KB=1MB/second , if you can save near to 50B per query, you will use 950*1000 = 950KB, in other words, you can send 5% more bytes over network without changing hardware ---
participants (2)
-
Richard Couture
-
Roberto Spadim