Help with sub query

[kevinfwb]

I'm having a little difficulty with a subquery and some help would be appreciated.

 

My existing query is as follows

SELECT SUM(sales) as sumSales, storePK FROM Sales WHERE Sales.storeNum=259272 AND Sales.week<=4 AND Sales.period=1 AND Sales.year=2006 GROUP BY storeNum

 

This gives me the proper sum, but not the proper storePK.  This query is part of a loop that subtracts 1 from the week and calculates the total sales for the period as of that week.

The next time the query is run it looks like this:

SELECT SUM(sales) as sumSales, storePK FROM Sales WHERE Sales.storeNum=259272 AND Sales.week<=3 AND Sales.period=1 AND Sales.year=2006 GROUP BY storeNum

 

The SUM is different(and correct), but I get the same storePK as the previous query.

 

Below is the query that I need, although I know the syntax isn't nearly correct, but it should give you the idea of what I'm looking for.

 

SELECT SUM(sales WHERE week <=4) as sumSales, storeNum, storePK FROM Sales WHERE Sales.storeNum=259272 AND Sales.week=4 AND Sales.period=1 AND Sales.year=2006 GROUP BY storeNum

 

and then the nex loop would be

 

SELECT SUM(sales WHERE week <=3) as sumSales, storeNum, storePK FROM Sales WHERE Sales.storeNum=259272 AND Sales.week=3 AND Sales.period=1 AND Sales.year=2006 GROUP BY storeNum

 

Thanks

-Kevin

 

[btherl]

It's difficult to help you without knowing your database structure.  Presumably there are many storePK for each storeNum.  What does storePK mean?  What does storeNum mean?  And how can you have a single storePK in your result when you are taking a sum that covers more than one storePK?

[kevinfwb]

storePK is the primary key for that line.  You are correct, there will be several storePK's, one for each entry.

 

The structure of my DB is as follows:

 

Sales = table name with the following fields:

 

storePK = auto increment primary key

storeNum = the store's unit number

week = the week for the fiscal year

period = the period for fiscal year

year = fiscal year

sales = unit sales

PTD = period to date

 

Our fiscal year consists of 13 periods with 4 weeks in each period.

They just recently they asked me to add period to date (PTD), which is the SUM of each week in the current period.

PTD for week 1 would be equal to week 1.  The PTD for week 2 would be the sum of week 1 sales + week 2 sales, etc..

Since PTD has just been added, all of the values are blank.  I'm working on a script to populate the field with the proper values.  I have a loop that starts with period 13 week 4 and works backwards for each store. 

 

I have 2 queries, the 1st one will query the DB to get the sum of sales for the current week and then the 2nd one adds the calculated value back into the table.

 

(When period = 3)

 

SELECT SUM(all sales where period <=3), storePK FROM Sales WHERE period=8 AND week=3 AND storeNum=207462 AND year=2007

 

Then the 2nd query updates the DB based on the primary key (storePK)

 

Does that clear anything up?

I thank you for your time.

 

-Kevin

[btherl]

Thanks, that clears up the database structure and your goals.

 

Is the reason you are selecting storePK because you want to update the most recent row from the rows you summed with the newly calculated PTD?  In which case you want the most recent storePK (which you can probably safely assume to be max(storePK)).  In which case:

 

SELECT SUM(sales) as sumSales, MAX(storePK) AS most_recent_storePK FROM Sales WHERE Sales.storeNum=259272 AND Sales.week<=4 AND Sales.period=1 AND Sales.year=2006 GROUP BY storeNum

 

Then you can use most_recent_storePK to do the update.  This is a bit dodgy because it may not always be true that the order of storePK matches the order of the entries.  Really you ought to be selecting the correct storePK seperately.  Another query won't hurt.