Re: 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 >