Silly query question

[Bob Stearns]

How do I (syntax please) write a query which returns the invoice header
data and the amount due which is the SUM(qty*each) from the details rows
with the same invoice number for all invoices in the header table?

create table invoices(
invoice_no integer not null
cust_no integer not null
created timestamp not null
paid timestamp not null

[Private Pyle]

Bob Stearns wrote:

How do I (syntax please) write a query which returns the invoice header
data and the amount due which is the SUM(qty*each) from the details rows
with the same invoice number for all invoices in the header table?

create table invoices(
invoice_no integer not null
cust_no integer not null
created timestamp not null
paid timestamp not null
.
.
.
primary key(invoice_no))
create table details
invoice_no integer not null
line_no smallint not null
item_no integer not null
qty smallint not null
each decimal(6,2) not null
.
.
.
primary key(invoice_no, line_no))

select a.invoice_no, a.whatever_other_invoice_data_you_need,
b.amount_due
from invoices a,
( select invoice_no, sum( each * qty ) as amount_due
from details
group by invoice_no ) b
where a.invoice_no = b.invoice_no
;

[jefftyzzer]

Hi, Bob:

Assuming you want the sums grouped by all columns in the select list
(i.e. sum by invoice by customer by created...), there's the
conventional query:

SELECT
I.INVOICE_NO,
<OTHER COLUMNS IF NEEDED>,
SUM(D.QTY*D.EACH) AMT_DUE
FROM
INVOICES I,
DETAILS D
WHERE
D.INVOICE_NO = I.INVOICE_NO
GROUP BY
I.INVOICE_NO,
<OTHER COLUMNS IF NEEDED>

Assuming you wanted all invoice data, and the sum at the invoice number
(only) level along for the ride:

SELECT
I.*,
INV_AGGR.AMT_DUE
FROM
INVOICES I,
TABLE
(
SELECT
I.INVOICE_NO INV_NO,
SUM(D.QTY*D.EACH) AMT_DUE
FROM
DETAILS D
WHERE
D.INVOICE_NO = I.INVOICE_NO
GROUP BY
I.INVOICE_NO
) INV_AGGR
WHERE
INV_AGGR.INV_NO = I.INVOICE_NO

Here's what I got, using your structure and my dummy data:

WITH INVOICES
(
INVOICE_NO,
CUST_NO,
CREATED,
PAID
)
AS (
VALUES
(1,1,'06/26/2006','07/26/2006'),
(2,2,'05/26/2006','06/26/2006')
),
DETAILS
(
INVOICE_NO,
LINE_NO,
ITEM_NO,
QTY,
EACH)
AS
(
VALUES
(1,1,435,10,25.00),
(1,2,890,25,.10),
(2,1,2345,1000,45.24),
(2,2,67657,45000,.02)
)
SELECT
I.*,
INV_AGGR.AMT_DUE
FROM
INVOICES I,
TABLE
(
SELECT
I.INVOICE_NO INV_NO,
SUM(D.QTY*D.EACH) AMT_DUE
FROM
DETAILS D
WHERE
D.INVOICE_NO = I.INVOICE_NO
GROUP BY
I.INVOICE_NO
) INV_AGGR
WHERE
INV_AGGR.INV_NO = I.INVOICE_NO;

INVOICE_NO CUST_NO CREATED PAID AMT_DUE

----------- ----------- ---------- ----------
---------------------------------
1 1 06/26/2006 07/26/2006
252.50
2 2 05/26/2006 06/26/2006
46140.00

2 record(s) selected.

Let me know if I've misinterpreted your requirements.

HTH,

--Jeff
Bob Stearns wrote:

How do I (syntax please) write a query which returns the invoice header
data and the amount due which is the SUM(qty*each) from the details rows
with the same invoice number for all invoices in the header table?

create table invoices(
invoice_no integer not null
cust_no integer not null
created timestamp not null
paid timestamp not null
.
.
.
primary key(invoice_no))
create table details
invoice_no integer not null
line_no smallint not null
item_no integer not null
qty smallint not null
each decimal(6,2) not null
.
.
.
primary key(invoice_no, line_no))

[Bob Stearns]

jefftyzzer wrote:

Hi, Bob:

Assuming you want the sums grouped by all columns in the select list
(i.e. sum by invoice by customer by created...), there's the
conventional query:

SELECT
I.INVOICE_NO,
<OTHER COLUMNS IF NEEDED>,
SUM(D.QTY*D.EACH) AMT_DUE
FROM
INVOICES I,
DETAILS D
WHERE
D.INVOICE_NO = I.INVOICE_NO
GROUP BY
I.INVOICE_NO,
<OTHER COLUMNS IF NEEDED>

Assuming you wanted all invoice data, and the sum at the invoice number
(only) level along for the ride:

SELECT
I.*,
INV_AGGR.AMT_DUE
FROM
INVOICES I,
TABLE
(
SELECT
I.INVOICE_NO INV_NO,
SUM(D.QTY*D.EACH) AMT_DUE
FROM
DETAILS D
WHERE
D.INVOICE_NO = I.INVOICE_NO
GROUP BY
I.INVOICE_NO
) INV_AGGR
WHERE
INV_AGGR.INV_NO = I.INVOICE_NO

Here's what I got, using your structure and my dummy data:

WITH INVOICES
(
INVOICE_NO,
CUST_NO,
CREATED,
PAID
)
AS (
VALUES
(1,1,'06/26/2006','07/26/2006'),
(2,2,'05/26/2006','06/26/2006')
),
DETAILS
(
INVOICE_NO,
LINE_NO,
ITEM_NO,
QTY,
EACH)
AS
(
VALUES
(1,1,435,10,25.00),
(1,2,890,25,.10),
(2,1,2345,1000,45.24),
(2,2,67657,45000,.02)
)
SELECT
I.*,
INV_AGGR.AMT_DUE
FROM
INVOICES I,
TABLE
(
SELECT
I.INVOICE_NO INV_NO,
SUM(D.QTY*D.EACH) AMT_DUE
FROM
DETAILS D
WHERE
D.INVOICE_NO = I.INVOICE_NO
GROUP BY
I.INVOICE_NO
) INV_AGGR
WHERE
INV_AGGR.INV_NO = I.INVOICE_NO;

INVOICE_NO CUST_NO CREATED PAID AMT_DUE

----------- ----------- ---------- ----------
---------------------------------
1 1 06/26/2006 07/26/2006
252.50
2 2 05/26/2006 06/26/2006
46140.00

2 record(s) selected.

Let me know if I've misinterpreted your requirements.

HTH,

--Jeff
Bob Stearns wrote:

>>How do I (syntax please) write a query which returns the invoice header
data and the amount due which is the SUM(qty*each) from the details rows
with the same invoice number for all invoices in the header table?

create table invoices(
invoice_no integer not null
cust_no integer not null
created timestamp not null
paid timestamp not null
.
.
.
primary key(invoice_no))
create table details
invoice_no integer not null
line_no smallint not null
item_no integer not null
qty smallint not null
each decimal(6,2) not null
.
.
.
primary key(invoice_no, line_no))

You got it exactly right. I have to map it into my case where I have two
different types of detail rows, one of which might not exist for any
particular invoice, so my FROM is extended with a LEFT OUTER JOIN TABLE
(SELECT ...) INV_AGGR2.

While having dinner and reading my latest SF book I came up with a
similar solution using WITH to generate the two tables of SUMs. If I
work out the details of that solution, I will post it.

[Bob Stearns]

Private Pyle wrote:

Bob Stearns wrote:

>>How do I (syntax please) write a query which returns the invoice header
data and the amount due which is the SUM(qty*each) from the details rows
with the same invoice number for all invoices in the header table?

create table invoices(
invoice_no integer not null
cust_no integer not null
created timestamp not null
paid timestamp not null
.
.
.
primary key(invoice_no))
create table details
invoice_no integer not null
line_no smallint not null
item_no integer not null
qty smallint not null
each decimal(6,2) not null
.
.
.
primary key(invoice_no, line_no))

select a.invoice_no, a.whatever_other_invoice_data_you_need,
b.amount_due
from invoices a,
( select invoice_no, sum( each * qty ) as amount_due
from details
group by invoice_no ) b
where a.invoice_no = b.invoice_no
;

How would this work if I had two types of details, one optional? Is the
comma form of the FROM equivalent to a LEFT OUTER JOIN or a JOIN?

[Knut Stolze]

Bob Stearns wrote:

>select a.invoice_no, a.whatever_other_invoice_data_you_need,
b.amount_due
from invoices a,
( select invoice_no, sum( each * qty ) as amount_due
from details
group by invoice_no ) b
where a.invoice_no = b.invoice_no
;

How would this work if I had two types of details, one optional? Is the
comma form of the FROM equivalent to a LEFT OUTER JOIN or a JOIN?

The comma performs a cross join (with the join condition placed in the WHERE
clause).

You could also combine the two detail tables in the subselect:

SELECT ...
FROM invoices AS a JOIN
( SELECT d1.invoice_no,
COALESCE(SUM( d1.each * d1.qty ), SUM( d2.each * d2.qty ))
FROM details AS d1 LEFT OUTER JOIN details2 AS d2 ON ...
GROUP BY ... ) AS b ON ...

It depends on your join criteria for the details tables if you can do that.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Brian Tkatch]

Bob Stearns wrote:

How do I (syntax please) write a query which returns the invoice header
data and the amount due which is the SUM(qty*each) from the details rows
with the same invoice number for all invoices in the header table?

create table invoices(
invoice_no integer not null
cust_no integer not null
created timestamp not null
paid timestamp not null
.
.
.
primary key(invoice_no))
create table details
invoice_no integer not null
line_no smallint not null
item_no integer not null
qty smallint not null
each decimal(6,2) not null
.
.
.
primary key(invoice_no, line_no))

Either a join, or a sub-select should do it.

SELECT
invoice_no,
cust_no,
created,
paid,
(
SELECT
SUM(qty * each)
FROM
details
WHERE
details.invoice_no = invoices.invoice_no
) Bill_Amount
FROM
invoices
B.