Re: A query for Report

I'm sorry for the SUM(Sales) field, please change it to COUNT(*). Jayme. -----Mensagem Original----- De: Jayme Jeffman Filho <jjeffmanweb@conex.com.br> Para: PHPDB <php-db@lists.php.net> Enviada em: terça-feira, 12 de setembro de 2000 19:17 Assunto: Re: [PHP-DB] A query for Report > Try the query bellow and let me know if it works for you : > > SELECT SUM(Sales),MONTH(SalesDate) AS Mnt, SalesMan > FROM SalesTable > WHERE SalesDate >= "2000/01/01" AND SalesDate <= "2000/10/01" > GROUP BY Mnt,Product ORDER BY Mnt > > It works for me using MySQL. > > I hope this helps. > Jayme > > > -----Mensagem Original----- > De: Deirdre Saoirse <deirdre@deirdre.net> > Para: Manuel <manuel@buyee.com.sg> > Cc: <php-db@lists.php.net> > Enviada em: terça-feira, 12 de setembro de 2000 17:15 > Assunto: Re: [PHP-DB] A query for Report > > > > On Sat, 9 Sep 2000, Manuel wrote: > > > > > I want to create a query with this result, salesman sales count for the > > > past xx months. > > > > > > Jan Feb Mar etc Total > > > salesman 1 XX XX XX XXX > > > > > sales Table > > > *********** > > > salesmancode > > > salesdate > > > > > > I use this to get the individual total but I do not know how to group > them > > > into last xx months and the totals. > > > > > > select salemancode,count(*) from sales group by salesmancode > > > > > > Total > > > salesman 1 xx > > > salesman 1 xx > > > > > > My problem: > > > 1. How to split them into months? > > > 2. How to total them up by Months(Jan, Feb, etc) and by Salesman > (Salesman > > > 1, Salesman 2, etc)? > > > > The totalling by months and by salesperson would be done with variables. > > > > However, no one's suggested the right sql for this kind of thing: joining > > a table to itself. > > > > It can be painful, but exactly how you get multiple columns out of data > > not arranged this way. As it's generally useful... > > > > First: > > > > select distinct(salesmancode) from sales > > > > Then, going through, assuming $salesmancode is the numeric code for that > > sales person (if it's not numeric, use quotes around the variable name): > > > > select count(a.salesdate), count(b.salesdate), count(c.salesdate) from > > sales a, sales b, sales c where a.salesmancode=$salesmancode and > > b.salesmancode=$salesmancode and c.salesmancode=$salesmancode and > > a.salesdate > '2000-01-01' and a.salesdate < '2000-02-01' and > > b.salesdate > '2000-02-01' and b.salesdate < '2000-03-01' and > > c.salesdate > '2000-03-01' and c.salesdate < '2000-04-01' > > > > You can actually do it as one big query, but the left outer join can be > > painful to debug; thus, I prefer it in two queries as above. Also, you can > > do it more neatly with variables for the date, etc., but that is left as > > an exercise for the reader. > > > > -- > > _Deirdre * http://www.sfknit.org * > > http://www.deirdre.net > > "More damage has been caused by innocent program crashes than by > > malicious viruses, but they don't make great stories." > > -- Jean-Louis Gassee, Be Newsletter, Issue 69 > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > > For additional commands, e-mail: php-db-help@lists.php.net > > To contact the list administrators, e-mail: php-list-admin@lists.php.net > > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net >