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