VReport general idea

[Beatrix Willius]

Oh my god!!! This is much too complicated. What about a simple pivot query? Group the data as you need it and do a pivot over it with the count.

Regards

Trixi

valentina-bounces+bwillius=gmx.de at lists.macserve.net schrieb am 26.02.2009 14:29:48:

> Hi Fabian,
> 
> thanks for this GREAT question, which will most probably be the first  
> item in our yet to write FAQ section on out german page :-)
> 
> On 2009-02-26, at 12:25, Fabian Eschrich wrote:
> 
> > But I have no idea how to start for a task like this:
> >
> > I want an table with statistical sums of our sales per month.
> > It should look like this:
> >
> >
> >          Jan    Feb    Mar
> > Invoices  10     11     12
> > Amount    1200   1300   1400
> >
> > Product A 1      1      1
> > Product B 2      2      3
> >
> >
> > The data is stored in 2 tables. One table holds records for all  
> > invoices, the other table holds all invoice items (each position on  
> > the invoices).
> >
> > I dont know if this can be done by a query.
> 
> 
> 
> While databases are smart and SQL is cool sometimes you need to write  
> code, much code, long, longe code - especially when thinking about  
> such pivot operations like you asked for.
> 
> So let's start pretty simple (I'm using here tables from one of my  
> apps so your table and column names may vary)
> 
> So first of all you want to count objects ... while a simple count(*)  
> may work in many situations for this sample, I choose another way, so  
> we start wit a simple "say here!" question
> 
> select
> 'count' rtitle,
> if(month(master_date) = 1, 1, 0) month1,
> if(month(master_date) = 2, 1, 0) month2,
> if(month(master_date) = 3, 1, 0) month3,
> if(month(master_date) = 4, 1, 0) month4,
> if(month(master_date) = 5, 1, 0) month5,
> if(month(master_date) = 6, 1, 0) month6,
> if(month(master_date) = 7, 1, 0) month7,
> if(month(master_date) = 8, 1, 0) month8,
> if(month(master_date) = 9, 1, 0) month9,
> if(month(master_date) = 10, 1, 0) month10,
> if(month(master_date) = 11, 1, 0) month11,
> if(month(master_date) = 12, 1, 0) month12
-- 
Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger01