Group By?

#1

Hi All,

I have the following query. For a few records, it returns duplicate
CUSTOMER_ID's, though the rest of the record values are different.

I want 1 record per CUSTOMER_ID, regardless of the other values.

SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type
FROM engine.customer_order co, engine.order_line ol, engine.product
p, product.newsletter_subscription ns
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.code = ns.code
AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
AND ol.status = 'Active'
ORDER BY co.customer_id;

Still working a solution.

#2

Comments embedded.
On Feb 8, 1:21*pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote:

Quote:

Hi All,

I have the following query. *For a few records, it returns duplicate
CUSTOMER_ID's, though the rest of the record values are different.

So the query is returning distinct records.

Quote:

I want 1 record per CUSTOMER_ID, regardless of the other values.

Why? What is the purpose of that requirement?

Quote:

* *SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
* * * * * DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type
* *FROM engine.customer_order co, engine.order_line ol, engine.product
p, product.newsletter_subscription ns
* *WHERE co.order_id = ol.order_id
* *AND ol.product_id = p.product_id
* *AND p.code = ns.code
* *AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
* *AND ol.status = 'Active'
* *ORDER BY co.customer_id;

Still working a solution.

Possibly you haven't tried hard enough:

select c_id, max(end_date), max(subscriber_type)
from
( SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type
FROM engine.customer_order co, engine.order_line ol,
engine.product
p, product.newsletter_subscription ns
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.code = ns.code
AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
AND ol.status = 'Active' )
group by c_id
order by c_id;

The query above should provide you one record per customer id; how
useful that data will be remains to be seen.

David Fitzjarrell

#3

Comments embedded.
On Feb 8, 1:21*pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote:

Quote:

Hi All,

I have the following query. *For a few records, it returns duplicate
CUSTOMER_ID's, though the rest of the record values are different.

So the query is returning distinct records.

Quote:

I want 1 record per CUSTOMER_ID, regardless of the other values.

Why? What is the purpose of that requirement?

Quote:

* *SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
* * * * * DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type
* *FROM engine.customer_order co, engine.order_line ol, engine.product
p, product.newsletter_subscription ns
* *WHERE co.order_id = ol.order_id
* *AND ol.product_id = p.product_id
* *AND p.code = ns.code
* *AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
* *AND ol.status = 'Active'
* *ORDER BY co.customer_id;

Still working a solution.

Possibly you haven't tried hard enough:

select c_id, max(end_date), max(subscriber_type)
from
( SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type
FROM engine.customer_order co, engine.order_line ol,
engine.product
p, product.newsletter_subscription ns
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.code = ns.code
AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
AND ol.status = 'Active' )
group by c_id
order by c_id;

The query above should provide you one record per customer id; how
useful that data will be remains to be seen.

David Fitzjarrell

#4

Comments embedded.
On Feb 8, 1:21*pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote:

Quote:

Hi All,

I have the following query. *For a few records, it returns duplicate
CUSTOMER_ID's, though the rest of the record values are different.

So the query is returning distinct records.

Quote:

I want 1 record per CUSTOMER_ID, regardless of the other values.

Why? What is the purpose of that requirement?

Quote:

* *SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
* * * * * DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type
* *FROM engine.customer_order co, engine.order_line ol, engine.product
p, product.newsletter_subscription ns
* *WHERE co.order_id = ol.order_id
* *AND ol.product_id = p.product_id
* *AND p.code = ns.code
* *AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
* *AND ol.status = 'Active'
* *ORDER BY co.customer_id;

Still working a solution.

Possibly you haven't tried hard enough:

select c_id, max(end_date), max(subscriber_type)
from
( SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type
FROM engine.customer_order co, engine.order_line ol,
engine.product
p, product.newsletter_subscription ns
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.code = ns.code
AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
AND ol.status = 'Active' )
group by c_id
order by c_id;

The query above should provide you one record per customer id; how
useful that data will be remains to be seen.

David Fitzjarrell

#5

Comments embedded.
On Feb 8, 1:21*pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote:

Quote:

Hi All,

I have the following query. *For a few records, it returns duplicate
CUSTOMER_ID's, though the rest of the record values are different.

So the query is returning distinct records.

Quote:

I want 1 record per CUSTOMER_ID, regardless of the other values.

Why? What is the purpose of that requirement?

Quote:

* *SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
* * * * * DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type
* *FROM engine.customer_order co, engine.order_line ol, engine.product
p, product.newsletter_subscription ns
* *WHERE co.order_id = ol.order_id
* *AND ol.product_id = p.product_id
* *AND p.code = ns.code
* *AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
* *AND ol.status = 'Active'
* *ORDER BY co.customer_id;

Still working a solution.

Possibly you haven't tried hard enough:

select c_id, max(end_date), max(subscriber_type)
from
( SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type
FROM engine.customer_order co, engine.order_line ol,
engine.product
p, product.newsletter_subscription ns
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.code = ns.code
AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
AND ol.status = 'Active' )
group by c_id
order by c_id;

The query above should provide you one record per customer id; how
useful that data will be remains to be seen.

David Fitzjarrell

#6

<amerar (AT) iwc (DOT) net> schreef in bericht
news:1c04d8cf-f3da-4b8b-9b0d-8e451c94c762 (AT) h11g2000prf (DOT) googlegroups.com...

Quote:

Hi All,

I have the following query. For a few records, it returns duplicate
CUSTOMER_ID's, though the rest of the record values are different.

I want 1 record per CUSTOMER_ID, regardless of the other values.

SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type
FROM engine.customer_order co, engine.order_line ol, engine.product
p, product.newsletter_subscription ns
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.code = ns.code
AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
AND ol.status = 'Active'
ORDER BY co.customer_id;

Still working a solution.

Then DON'T select the other values (if you don't care about them)....

Shakespeare

#7

<amerar (AT) iwc (DOT) net> schreef in bericht
news:1c04d8cf-f3da-4b8b-9b0d-8e451c94c762 (AT) h11g2000prf (DOT) googlegroups.com...

Quote:

Hi All,

I have the following query. For a few records, it returns duplicate
CUSTOMER_ID's, though the rest of the record values are different.

I want 1 record per CUSTOMER_ID, regardless of the other values.

SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type
FROM engine.customer_order co, engine.order_line ol, engine.product
p, product.newsletter_subscription ns
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.code = ns.code
AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
AND ol.status = 'Active'
ORDER BY co.customer_id;

Still working a solution.

Then DON'T select the other values (if you don't care about them)....

Shakespeare

#8

<amerar (AT) iwc (DOT) net> schreef in bericht
news:1c04d8cf-f3da-4b8b-9b0d-8e451c94c762 (AT) h11g2000prf (DOT) googlegroups.com...

Quote:

Hi All,

I have the following query. For a few records, it returns duplicate
CUSTOMER_ID's, though the rest of the record values are different.

I want 1 record per CUSTOMER_ID, regardless of the other values.

SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type
FROM engine.customer_order co, engine.order_line ol, engine.product
p, product.newsletter_subscription ns
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.code = ns.code
AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
AND ol.status = 'Active'
ORDER BY co.customer_id;

Still working a solution.

Then DON'T select the other values (if you don't care about them)....

Shakespeare

#9

<amerar (AT) iwc (DOT) net> schreef in bericht
news:1c04d8cf-f3da-4b8b-9b0d-8e451c94c762 (AT) h11g2000prf (DOT) googlegroups.com...

Quote:

Hi All,

I have the following query. For a few records, it returns duplicate
CUSTOMER_ID's, though the rest of the record values are different.

I want 1 record per CUSTOMER_ID, regardless of the other values.

SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type
FROM engine.customer_order co, engine.order_line ol, engine.product
p, product.newsletter_subscription ns
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.code = ns.code
AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
AND ol.status = 'Active'
ORDER BY co.customer_id;

Still working a solution.

Then DON'T select the other values (if you don't care about them)....

Shakespeare

#10

select customer_id, end_date, subscr_type
from (
SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type, row_number() over (partition by customer_id order by
rowid) ordering
FROM engine.customer_order co, engine.order_line ol, engine.product
p, product.newsletter_subscription ns
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.code = ns.code
AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
AND ol.status = 'Active'
)
where ordering=1
ORDER BY customer_id;

<amerar (AT) iwc (DOT) net> wrote

Quote:

Hi All,

I have the following query. For a few records, it returns duplicate
CUSTOMER_ID's, though the rest of the record values are different.

I want 1 record per CUSTOMER_ID, regardless of the other values.

SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
p.quantity, p.quantity_uom) end_date,
DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
'Paid') subscr_type
FROM engine.customer_order co, engine.order_line ol, engine.product
p, product.newsletter_subscription ns
WHERE co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.code = ns.code
AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
AND ol.status = 'Active'
ORDER BY co.customer_id;

Still working a solution.