-
need help with a query
Am building a mySQL view so I can report on sales, but I want to be able to account for months or weeks that have no sales. Am using the orders table to build the view. The query below will pull the info for the months/weeks that have sales, but it is kinda hard to graph with holes in the data. Optimally I would have rows for all 52 weeks of each year, with or without sales in that week
select count(invoice) as totalsales,
year(`orders`.`shipdate`) AS `shipyear`,
month(`orders`.`shipdate`) AS `shipMonth`,
week(`orders`.`shipdate`,3) AS `shipWeek`,
sum(`orders`.`total`) AS `total`,
sum(`orders`.`tax`) AS `tax`,
sum(`orders`.`shiptotal`) AS `shiptotal`
from `orders` where (`orders`.`shipdate` is not null)
group by year(`orders`.`shipdate`),month(`orders`.`shipdate `),week(`orders`.`shipdate`,3)
order by year(`orders`.`shipdate`),month(`orders`.`shipdate `),week(`orders`.`shipdate`,3);
-
hi
your question is not clear, post it one more time...!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|