group by timestamp

[toodi4]

I've got a table that has a timestamp column. I'm trying to group the data by month and year. This is the query I am using:

SELECT FROM_UNIXTIME( week, '%Y %m' ) AS time, store, sales FROM sales_tracking GROUP BY time, store;

There is actually only one timestamp per day, as this is taking daily data tallies.

The problem is, the output I get is only the data for the first timestamp of each month. So in other words, the data is for the first day of each month.

I've tried working this a number of ways, but I can't seem to get the right output. Is there something about grouping with the FROM_UNIXTIME value that is the problem?

[Weedpacket]

By "timestamp" column do you mean a MySQL timestamp? That's not the same thing as a Unix timestamp.

You're also grouping by time (and store), so you will only get one record for each distinct combination of the two columns. Are you saying that you only get one record per month even though you should be getting one record each for several stores?

Another thing is that you have no aggregate function on the sales column; either you want all of the values from this column (in which case GROUP BY is the wrong clause to be using and you probably mean ORDER BY) or you're missing the function: the query only returns a single row per time/store combination, so there needs to be some way to take all of the sales for each combination and reduce them to a single value. The DBMS should have complained about this and refused to run the query at all, but I wouldn't be surprised to find that MySQL just picks a something at random.

[toodi4]

thanks for your response weekpacket. But it turns out I just made a stupid omission. I forgot to do sum(sales) and instead just had 'sales.'