Advice on possible self join or subquery. 2

[petrosky]

Hi there,

I am quite new to MYSQL and come from a MS Access background.

I have a sales transaction table and can create a query that will give me aggregate totals for a certain period of time for various product groups.

SELECT
groupslink.Group as GroupName,
sum(Quantity) as QuantityTotal,
sum(Sale) as SaleTotal
FROM groupslink INNER JOIN translog
ON groupslink.GroupsGroup = translog.Group
WHERE translog.Date between '2008-02-01' and '2008-02-28'
and translog.Store = 1
GROUP BY groupslink.Group

This will output something like the following...

GroupName      QuantityTotal     SaleTotal
Accessories    10                100.00
Cameras        15                2300.00
etc.

What I am looking for is a query that will provide another column of data that shows last years sales totals for the various groups.

In Access I would just include another aggregate query in my query to display that value as another column...which leads me to suspect I need a sub-query for the different period of time.

I can't find any examples to help me..so I hope someone has some ideas for me.

Thanks in advance.

Peter.



Remember- It's nice to be important,
but it's important to be nice

 

[r937]

there are several ways to approach this, but the main problem is how to handle the situation where some products were sold last year but not this year, and some were sold this year by not last year

the neatest way, in my opinion, is to have two queries -- one for this year, one for last year -- and arrange the SELECT clauses so that their totals are in separate pairs of buckets

then union the results sets of the two queries, such that the UNION is a subquery nested in another query, and do a GROUP BY in the outer query, to collapse the two sets of results into one

this conveniently handles the missing data issue from either year

SELECT GroupName
     , SUM(ThisYearQuantity) AS ThisYearQuantityTotal   
     , SUM(ThisYearSale)     AS ThisYearSaleTotal       
     , SUM(LastYearQuantity) AS LastYearQuantityTotal   
     , SUM(LastYearSale)     AS LastYearSaleTotal       
  FROM (
       SELECT groupslink.Group AS GroupName
            , SUM(Quantity)    AS ThisYearQuantity   
            , SUM(Sale)        AS ThisYearSale       
            , 0                AS LastYearQuantity   
            , 0                AS LastYearSale       
         FROM translog
       INNER 
         JOIN groupslink 
           ON groupslink.GroupsGroup = translog.Group
        WHERE translog.Date between [red]'2008-02-01' and '2008-02-28'[/red]
          AND translog.Store = 1
       GROUP 
           BY groupslink.Group
       UNION ALL
       SELECT groupslink.Group AS GroupName
            , 0                AS ThisYearQuantity   
            , 0                AS ThisYearSale       
            , SUM(Quantity)    AS LastYearQuantity   
            , SUM(Sale)        AS LastYearSale       
         FROM translog
       INNER 
         JOIN groupslink 
           ON groupslink.GroupsGroup = translog.Group
        WHERE translog.Date between [red]'2007-02-01' and '2007-02-28'[/red]
          AND translog.Store = 1
       GROUP 
           BY groupslink.Group
       ) AS data
GROUP
    BY GroupName

r937.com | rudy.ca

 

[petrosky]

Rudy,

Firstly can I just say WOW!

That is an amazing query. I am really trying to get my head around it as I am sure this will benefit me in the future.

Have a star for your efforts. Just out of curiosity, how did you go about mentally putting the queries together?

Thank you.

Peter.



Remember- It's nice to be important,
but it's important to be nice

 

[r937]

well, i've been writing SQL for over 20 years, so it comes fairly easily for me now...



r937.com | rudy.ca

 

[petrosky]

mmm,

Looks like I have a long and steep learning curve ahead of me!



Thanks again.

Remember- It's nice to be important,
but it's important to be nice

 

[towerbase]

Once again, a very useful posting from Rudy. I've wondered how to tackle similar problems in the past. A well deserved star from me.

Andrew
Hampshire, UK