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
