Query for Best Sales Day? 1

[DrumAt5280]

I am confused how I can write a query to return the best sales day in terms of the amount and the date.

Table: sales
date field: dateStamp
price: price

My attempt did not work:

SELECT count(*) as totalSales, dateStamp
FROM sales
GROUP BY dateStamp
ORDER BY price desc Limit 1

 

[feherke]

Hi

Are you sure you want to [tt]order by[/tt] price and not by totalSales ?

Feherke.
feherke.ga

 

[DrumAt5280]

Thanks Feherke

I changed it to this:

SELECT count(*) as totalSales, dateStamp
FROM sales
GROUP BY dateStamp
ORDER BY totalSales desc Limit 1

This is what it returned:
totalSales: 7 | dateStamp: 2012-10-21 07:33:25

I know my record sales day is higher than 7.

Do you think it could be how the date is formatted that is returning a smaller set of sales?

 

[SkipVought]

Count? Really?

[pre]
SELECT SUM(price) as totalSales, dateStamp
FROM sales
GROUP BY dateStamp
ORDER BY SUM(price) desc Limit 1
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[feherke]

Hi

date field: dateStamp

You wrote date, so I just let you [tt]group by[/tt] it... But looks like there you got the second with the most activity.

Just [tt]date()[/tt] it :

[b]SELECT[/b] count[teal](*)[/teal] [b]as[/b] totalSales[teal],[/teal] [maroon]date[/maroon][teal]([/teal]dateStamp[teal])[/teal]
[b]FROM[/b] sales
[b]GROUP BY[/b] [maroon]date[/maroon][teal]([/teal]dateStamp[teal])[/teal]
[b]ORDER BY[/b] totalSales [b]desc Limit[/b] [purple]1[/purple]

Feherke.
feherke.ga

 

[DrumAt5280]

Thanks guys, much appreciated, that worked great!

 

[Olaf Doschke]

What really is most important is the profit, isn't it?

SELECT Sum(price-cost) as totalProfit, date(dateStamp)
FROM sales
GROUP BY date(dateStamp)
ORDER BY totalProfit desc Limit 1

Anyway, this would need every sales item to be tagged with a cost (production cost, acquisition value, time effort, ...) and not only a price.

Bye, Olaf.

 

[ChrisHirst]

If you only want the largest sales value.

SELECT MAX(totalSales), date(dateStamp)  FROM sales;

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum