Walter,
Not entirely sure what the problem is. You can keep on JOINing extra
tables (provided you join on appropriate columns - foreign keys,
usually) until the cows come home. Bear in mind that since you are using
GROUP BY, every "column" in the SELECT clause needs to be either column
in the GROUP BY, or an aggregate function.
Not sure if this helps...
Cheers, Iain
Walter Vaughan wrote:
Okay, I'll admit my SQL skill are pre-newbie, but I'm kinda at a loss.
I'm trying to build some reports, and I am begging for some direction.
The following query works, but I need to add more columns...
SELECT
"party_group"."group_name" AS "Company_Name"
max("party_classification"."party_classification_group_id") as
"Discount Type",
sum("order_item"."unit_price")as "Discounted Amount",
sum("order_item"."unit_list_price")as "Retail Amount",
count( distinct "order_header"."order_id")as "Number Of Orders"
FROM
"party" INNER JOIN "party_classification" ON "party"."party_id" =
"party_classification"."party_id"
INNER JOIN "order_header" ON "party"."party_id" =
"order_header"."bill_to_party_id"
INNER JOIN "party_group" ON "party"."party_id" =
"party_group"."party_id"
INNER JOIN "order_item" ON "order_header"."order_id" =
"order_item"."order_id"
WHERE
party_classification_group_id LIKE 'DEALER%'
AND "order_item"."status_id" = 'ITEM_COMPLETED'
GROUP BY
"party_group"."group_name"
ORDER BY
"party_group"."group_name" ASC
I need to add in columns like phone numbers and primary contact names,
city states, etc...
So I guess I need subqueries or some sort of SQL pixie dust, cause I
can't figure out how to query in two different directions (order info
and customer info).
Help. Anyone got any example SQL scripts around? Or links to examples
that emulate the ofbiz database layout?
--
Walter
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.15.31 - Release Date: 13/11/2007 12:00 AM
