Miva Merchant - Customer Order History Report

miva-customer-order-history-report.sql

SELECT
	customer.login AS "Account Login",
	customer.pw_email AS "Account Email",

	customer.ship_fname AS "Ship. First Name",
	customer.ship_lname AS "Ship. Last Name",
	customer.ship_email AS "Ship. Email",
	customer.ship_comp AS "Ship. Company",
	customer.ship_phone AS "Ship. Phone",
	customer.ship_fax AS "Ship. Fax",
	customer.ship_addr AS "Ship. Address",
	customer.ship_city AS "Ship. City",
	customer.ship_state AS "Ship. State",
	customer.ship_zip AS "Ship. Zip",
	customer.ship_cntry AS "Ship. Country",

	customer.bill_fname AS "Bill. First Name",
	customer.bill_lname AS "Bill. Last Name",
	customer.bill_email AS "Bill. Email",
	customer.bill_comp AS "Bill. Company",
	customer.bill_phone AS "Bill. Phone",
	customer.bill_fax AS "Bill. Fax",
	customer.bill_addr AS "Bill. Address",
	customer.bill_city AS "Bill. City",
	customer.bill_state AS "Bill. State",
	customer.bill_zip AS "Bill. Zip",
	customer.bill_cntry AS "Bill. Country",

	SUM(orders.total) AS "Total Spent",
	COUNT(orders.id) AS "Number of Orders",
	FROM_UNIXTIME(MIN(orders.orderdate)) AS "Date of First Order",
	FROM_UNIXTIME(MAX(orders.orderdate)) AS "Date of Last Order"
FROM s01_Customers AS customer
JOIN s01_Orders AS orders
	ON customer.id = orders.cust_id
GROUP BY customer.id
ORDER BY customer.login ASC