SQL query question

Question

I'm trying to do something in a query that I've never done before. it probably requires variables, but i've never done that, and I'm not sure that it does.

What I want is to get a list of sales, grouped first by affiliate, then by it's month.

I can do that, but here's the twist... I don't want the month, but month 1, month 2, month 3...

And those aren't Jan, feb, march, but the number of months since the day of first sale.

Is this possible in a query at all, or do I need to do this in my code.

Oh, mysql 5.1.something...

Answer 1

Sure, just write an expression in SQL that generates the number of months since the first sale (Do you mean the first sale for that afiliate? If so, you'll need a subquery) And since you say you want a list of sales, I assume you don't really want to "Group By" affilaite and monthcount, you just want to Sort, or Order By those values)

If you wanted the Average sales amount, or the Count of sales, or some other Aggregate function of sales data, then you would be doing a "Group By"...

And I don't think you need to worry about sorting by the number of months, you can simply sort by the difference between each sales date and the rearliest sale date for each affiliate. (If you wanted to apply a third sorting rule, after the sales date sort, then you would need to be more careful.)

   Select * From Sales S
   Order By Affiliate, 
         SalesDate - (Select Min(SalesDate)
                      From Sales 
                      Where Affiliate = S.Affiliate)

Or, if you really need it to be by the difference in months

   Select * From Sales S
   Order By Affiliate, 
         Month(SalesDate) - 
              (Select Month(Min(SalesDate))
               From Sales 
               Where Affiliate = S.Affiliate)

Answer 2

This is possible in standard SQL if you use what I like to call "SQL gymnastics". It can be done with subqueries.

But it looks incredibly ugly, is hard to maintain and it's really not worth it. You're far better off using one of the many programming languages that wrap SQL (such as PL/SQL) or even a general purpose language that can call SQL (such as Python).

The result will be in two languages but will be all the more understandable than the same thing written in just SQL.