I am trying to develop a sales report coming off our postgresql server and am rumming into some problems. Basically I just want to group by the customer name and view a customers orders and sales from the previous month compared to the current month. I know in Access I could use an IIF command, but it isn't supported in Postgres. I think I need to use the CASE WITH function but am not sure how to use it properly. The SQL code I am working with is:
- SELECT customer.name AS "Company", customer.businessnumber AS "RRID", customer.dcp_elec_price AS "DCP Pricing", customer.dcl_elec_price AS "DCL Pricing",
-
Count(IIf(orderitems.order_date BETWEEN '2009-06-01' AND '2009-06-17', orderitems.customer_id)) AS "Prev Month Total Orders",
-
Count(IIf(orderitems.order_date BETWEEN '2009-07-01' AND '2009-07-17', orderitems.customer_id)) AS "Curr Month Total Orders",
-
Avg(IIf(orderitems.order_date BETWEEN '2009-06-01' AND '2009-06-17', orderitems.sellprice)) AS "Prev Month Avg Sale Price",
-
Avg(IIf(orderitems.order_date BETWEEN '2009-07-01' AND '2009-07-17', orderitems.sellprice)) AS "Curr Month Avg Sale Price",
-
Sum(IIf(orderitems.order_date BETWEEN '2009-06-01' AND '2009-06-17', orderitems.sellprice)) AS "Prev Total Sales ($)",
-
Sum(IIf(orderitems.order_date BETWEEN '2009-07-01' AND '2009-07-17', orderitems.sellprice)) AS "Curr Month Total Sales ($)"
-
FROM customer, orderitems
-
WHERE customer.id = orderitems.customer_id
-
GROUP BY customer.name, customer.businessnumber, customer.dcp_elec_price, customer. dcl_elec_price
-
ORDER BY customer.name
-
Also I thought maybe I need to join the order items table twice with an alias and use two different date ranges, however, doing this caused my aggregates to group incorrectly and produced inaccurate results.
I'm new the the group, but thanks in advance for the help.
