Silly query question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Stearns - 27 Jul 2006 00:45 GMT

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))

Reply to this Message

Private Pyle - 27 Jul 2006 02:47 GMT

> 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
[quoted text clipped - 19 lines]
> .
> 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
;

Reply to this Message

Bob Stearns - 27 Jul 2006 04:42 GMT

>>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
[quoted text clipped - 28 lines]
> 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?

Reply to this Message

Knut Stolze - 27 Jul 2006 07:32 GMT

>> select a.invoice_no, a.whatever_other_invoice_data_you_need,
>> b.amount_due
[quoted text clipped - 7 lines]
> 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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

jefftyzzer - 27 Jul 2006 02:50 GMT

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

Reply to this Message

Bob Stearns - 27 Jul 2006 04:40 GMT

> Hi, Bob:
>
[quoted text clipped - 127 lines]
>> .
>> 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.

Reply to this Message

Brian Tkatch - 27 Jul 2006 14:06 GMT

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.

Reply to this Message