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.
1 3647 rski 700
Expert 512MB
What postgres version do you use?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: x |
last post by:
Here's my problem...
I have a file on many many users remote machines (with different OS's,
etc) and I want to have my program test the date that the file that
they are using, against the date...
|
by: Donnal Walter |
last post by:
I was very surprised to discover that
>>> import datetime
>>> x = datetime.date(2004, 9, 14)
>>> y = datetime.datetime(2004, 9, 14, 6, 43, 15)
>>> print x == y
True
How can these two...
|
by: kinne |
last post by:
The following code is supposed to reverse the date in "yyyy-mm-dd" format,
but it produces different results in Firefox 1.0 and in Internet Explorer
6SP1. In Firefox, the result is correct...
|
by: Sami |
last post by:
Please bear with me, and if you answer this question, please do it
step by step. I am new at Access, not at all sophisticated. I am
using Office XP. This will need to be read in Access for...
|
by: Steve Elliott |
last post by:
I have a query set up to gather together data between two specified dates.
Shown in the query column as:
Between #24/09/2004# And #01/10/2004#
Is it possible to enter several different date...
|
by: Chéraaar |
last post by:
Hi,
I have the following problem:
In one file (addresses) I have a bunch of lines like this:
New 0x8048240 Old 0x0: jne 804824a
New 0x8048253 Old 0x0: je 8048293
New 0x80482c7 Old 0x0: jp...
|
by: Dennis |
last post by:
I have a public variable in a class of type color declared as follows:
public mycolor as color = color.Empty
I want to check to see if the user has specified a color like;
if mycolor =...
|
by: colincolehour |
last post by:
I am new to Python and am working on my first program. I am trying to
compare a date I found on a website to todays date. The problem I have
is the website only shows 3 letter month name and the...
|
by: anagai |
last post by:
I just want to check if a date entered in a textbox is equal to the
current system date. I set the date object from the input field like
this:
dt1=new Date('10/01/2007');
the current system...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |