Sales Totals By Day. By month worked, why not day?

[imstillatwork]

The query below works great for gathering monthly totals for a givin year.

I want to modify it to gather daily totals for a givin month and year.

It seems to error as soon as I try to use day() in the group by instead of month()

	SELECT
		o.order_ID
		,o.order_Payment_Date
		,o.order_total
		,SUM(o1.order_total) AS sumtotal
	FROM nwcs_orders AS o
	
	LEFT JOIN nwcs_orders AS o1
	ON o1.order_id = o.order_id

	WHERE year(o.order_Payment_Date) = #MyYearVariable#
	
	GROUP BY
		month(o.order_Payment_Date)

	ORDER BY
		month(o.order_Payment_Date)

I though it would be as simple as

	WHERE month(o.order_Payment_Date) = MyMonthVariable#
	AND year(o.order_Payment_Date) = MyYearVariable#
	GROUP BY
		day(o.order_Payment_Date)

	ORDER BY
		day(o.order_Payment_Date)

When I change

WHERE year(o.order_Payment_Date) = MyMonthVariable#

to

WHERE month(o.order_Payment_Date) = MyMonthVariable#

the query works, but give the wrong data (of course) but as soon as I change the group by, it fails.

???????????

Kevin
THANKS!

 

[imstillatwork]

p-p-p-pleeeease?

 

[TonyGroves]

How does the query fail?
What variables are you using for the day and month?

 

[sylve]

You are referencing the same table twice, is that normal? From what i can tell, your GROUP BY is not the issue, it's the whole SELECT that could be confused.

SELECT 
DAY(order_Payment_Date),
COUNT(order_ID) as totalorders,
SUM(order_total) as sumtotal
FROM 
nwcs_orders
WHERE 
year(o.order_Payment_Date) = #MyYearVariable#
AND
month(o.order_Payment_Date) = MyMonthVariable#
GROUP BY 
DAY(order_Payment_Date)
ORDER BY
DAY(order_Payment_Date)

When you group, every field of that table that could be unique should have a group function. The select i typed above should give you the total of orders and payments for each day of a given month.

If this isnt exactly what you want, post again with more details. A [RED]describe nwcs_orders[/RED] is useful too!

Good luck

 

[imstillatwork]

My values for year and month would be 4 digit year 1999, 2004, etc and 2 digit month 01,02,10,12, etc..


The first query gives my this

row     order_payed_date        sumtotal
1  	2005-02-18 00:00:00.0  	71.55
2 	2005-03-23 00:00:00.0 	12.99
3 	2005-04-12 00:00:00.0 	333.99
4 	2005-07-11 00:00:00.0 	29.99
5 	2005-10-12 00:00:00.0 	15.99

I need the date reference for graphing the results. the day on this query doesn't matter.

It selects the table twice to get a sum per month, and get a date for that sum. (only need a month / sum pair based on a given year) This was from someone else on this board.

Now I want a day / sum result based on a given month and year. why can't we just modify the query that is working so well? I would have though we could just change the month / year group by and wheres to month year and day as needed.

as for the db here is whats in it that we need.

order_id (if you need to use it, is the auto-id)
order_payment_date (date order was payed)
order_subtotal (total cost of products in order (not shipping))

From that, I want to be able to specify a year, and month, and get a recordset like:

row     order_payed_date        sumtotal
1  	2005-02-10 00:00:00.0  	71.55
2 	2005-02-11 00:00:00.0 	12.99
3 	2005-02-12 00:00:00.0 	9.99
4 	2005-02-13 00:00:00.0 	29.99
5 	2005-02-14 00:00:00.0 	15.99

I've been using SQL for a few years, can get the data I need from multiple relational tables, but never had to work with dates like this. It's all new to me!

 

[imstillatwork]

I guess here is a root of the problem

This Works. Gives the data I would expect

	SELECT
		month(order_Payment_Date)
	
	FROM nwcs_orders

	WHERE year(order_Payment_Date) = #yearvar#
	AND  month(order_Payment_Date) = #monthvar#

	ORDER BY
		month(order_Payment_Date)

AS does this

SELECT
		month(order_Payment_Date)
	
	FROM nwcs_orders

	WHERE year(order_Payment_Date) = #yearvar#
	AND  month(order_Payment_Date) = #monthvar#
		
	GROUP BY
		month(order_Payment_Date)

	ORDER BY
		month(order_Payment_Date)

BUT this will not? ?

SELECT
		day(order_Payment_Date)
	
	FROM nwcs_orders

	WHERE year(order_Payment_Date) = #yearvar#
	AND  month(order_Payment_Date) = #monthvar#


	ORDER BY
		month(order_Payment_Date)

All i did was change month() to day()
and it fails with: " Syntax error or access violation: You have an error in your SQL syntax.


VENDORERRORCODE 1064
SQLSTATE 42000
"

 

[imstillatwork]

I don't get it because SECOND, HOUR, MONTH, YEAR, QUARTER, etc all work fine, but simple using DAY will not work, when the data in the field clearly supports it = 2005-02-10 00:00:00.0

ok.....


I was reading more of the MySQL manual and DAY() is only available in 4.1 + I need to use DAYOFMONTH. :/

 

[imstillatwork]

Funny how I can fix my own problem by complaining about it enough! thanks for the replies.

About the JOIN, I don't know why we did that, the other guy that helped me before. It is not needed. all seems good now.