Help with query

[kevinfwb]

I'm stumped and I need some help with a MySQL query.

 

I have a table that keeps track of sales and has the following fields:

 

storeNum = the company assigned number to the stores

week - the week of the recorded sales

period - the period of the recorded sales

year - the year of the recorded sales

month - the month the recorded sales

sales - the dollar amount of the sales

 

Our fiscal year is divided into 13 periods with 4 weeks in each period.

 

I'm trying to create a query that will give me all 4 weeks of sales in a given period.

 

Store    Period 1      Week 1    Week 2  Week 3  Week 4

1234        1            $8400      $6500    $7412    $7142

 

I have the following query, but it gives me 4 separate rows instead of one row.

 

SELECT storeNum, sales FROM Sales WHERE period = 12 AND year = 2007;

 

I hope that I've provided enough information as to what I'm looking for.  I know that it's not an incredibly difficult query, I'm just stumped at the moment.

 

Thanks

 

-Kevin 

 

[fenway]

Why does it matter how many rows it gives back? You can format the output however you desire.

[kevinfwb]

I'm not sure I follow your question.

We have about 165 stores and the results are sent to an html table.

If I can get the query to return storeNum, week 1, week 2, week 3, week 4

I'll still have 165 rows returned instead of 660.. wouldn't that be far more efficient?

[fenway]

I'll still have 165 rows returned instead of 660.. wouldn't that be far more efficient?

Not really, becauase (a) the amount of data is the same and (b) getting mysql to "flip" the rows like that would be more expensive.