## Get column total on same line

### Get column total on same line

Is there a better way of doing this?  I thought there might be a better way
to calculate the JobTotalCost rather then basically running the same query
twice.

Note:  POTotalCost is actually a SUM(xxxx) of some other fields.  I simplied
this example a bit.

Thanks

================
Sample Query:
================
SELECT ReceiptID, PurchaseOrderID, POTotalCost,
JobTotalCost =
(
SELECT COALESCE(SUM(POTotalCost), 0)
FROM #ReceiptTest
)
FROM #ReceiptTest

================
Desired Results:
================

ReceiptID                PurchaseOrderID          POTotalCost
JobTotalCost
------------------------ ------------------------ --------------------- ----
------------------------------------
00000137                 1000009                  18.6400
20.9800
00000137                 1000022                  2.3400
20.9800

================
DDL
================
CREATE TABLE #ReceiptTest (
ReceiptID varchar (24) NOT NULL,
PurchaseOrderID varchar(24) NOT NULL,
POTotalCost decimal(19,4) NOT NULL
CONSTRAINT pk#ReceiptTest UNIQUE  NONCLUSTERED
(
[ReceiptID],
[PurchaseOrderID]
)
)

INSERT INTO #ReceiptTest VALUES ('00000137', '1000009', 18.64)
INSERT INTO #ReceiptTest VALUES ('00000137', '1000022', 2.34)

SELECT ReceiptID, PurchaseOrderID, POTotalCost,
JobTotalCost =
(
SELECT COALESCE(SUM(POTotalCost), 0)
FROM #ReceiptTest
)
FROM #ReceiptTest

--DROP TABLE #ReceiptTest

### Get column total on same line

This is not a correlated subquery, so it will run only once.  It is also
presenting two levels of aggregation in a single row.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

### Get column total on same line

You haven't specified a correlation so your total column will actually give
the total for the entire table. Was that intended or was it supposed to be
the total for each receiptid?

SELECT receiptid, purchaseorderid, pototalcost,
(SELECT COALESCE(SUM(pototalcost), 0)
FROM ReceiptTest
WHERE receiptid = R.receiptid)
AS jobtotalcost
FROM ReceiptTest AS R

Alternatively:

SELECT R.receiptid, R.purchaseorderid, R.pototalcost,
COALESCE(T.jobtotalcost,0) AS jobtotalcost
FROM ReceiptTest AS R
JOIN
(SELECT receiptid, SUM(pototalcost) AS jobtotalcost
FROM ReceiptTest
GROUP BY receiptid) AS T
ON R.receiptid = T.receiptid

This output is really a report and any reporting application can easily add
totals and subtotals to your reports without you having to define them in a
query.

You can also use CUBE/ROLLUP to produce totals:

SELECT receiptid, purchaseorderid, SUM(pototalcost)
FROM ReceiptTest
GROUP BY receiptid, purchaseorderid
WITH ROLLUP

--
David Portas
SQL Server MVP
--

### Get column total on same line

Thanks for the information David (and Joe).

The query isn't for a report but rather it is being used to allocate freight
costs based on the cost of the PO verses all POs received in the Receiving
Job.

After testing your queries, I realized that it didn't really matter how I
retreived the JobTotalCost, because the batch percentage was bascially the
same.  I thought there might be a way to do the SUM without doing the
subquery but it doesn't seem possible.  (Hey, every little bit of savings
count in the end!)

I ended up using the following (a little more complicated then the sample
provided, but the idea is the same):

UPDATE ReceiptOrder SET FreightCharges =
CAST(CASE WHEN Q1.POTotalCost > 0.00 AND Q1.JobTotalCost > 0.00 THEN
CASE WHEN (Q1.POTotalCost / Q1.JobTotalCost) > 0.00 THEN
CAST('100.00' AS decimal(19,4)) * (Q1.POTotalCost / Q1.JobTotalCost)
ELSE 0.00
END
ELSE 0.00 END AS decimal(19,2))
FROM ReceiptOrder
INNER JOIN
(
SELECT ROL.ReceiptID, ROL.PurchaseOrderID,
SUM(CAST(POL.UnitPrice AS money) * CASE WHEN POL.ConvertUnitsFg = 'Y' AND
CAST(POL.ConvertMultiplier AS decimal(19,4)) > 1.0 THEN CAST(ROL.Quantity AS
decimal(19,4)) / CAST(POL.ConvertMultiplier AS decimal(19,4)) ELSE
CAST(ROL.Quantity AS decimal(19,4)) END) AS POTotalCost,
COALESCE(T.JobTotalCost, 0) AS JobTotalCost
FROM ReceiptLineDetail ROL
INNER JOIN PurchaseOrderLine POL ON POL.PurchaseOrderID =
ROL.PurchaseOrderID AND POL.LineNumber = ROL.LineNumber
LEFT JOIN (
SELECT ReceiptID, SUM(CAST(POL1.UnitPrice AS money) * CASE WHEN
POL1.ConvertUnitsFg = 'Y' AND CAST(POL1.ConvertMultiplier AS decimal(19,4))

Quote:> 1.0 THEN CAST(ROL1.Quantity AS decimal(19,4)) /

CAST(POL1.ConvertMultiplier AS decimal(19,4)) ELSE CAST(ROL1.Quantity AS
decimal(19,4)) END) AS JobTotalCost
FROM ReceiptLineDetail ROL1
INNER JOIN PurchaseOrderLine POL1 ON POL1.PurchaseOrderID =
ROL1.PurchaseOrderID AND POL1.LineNumber = ROL1.LineNumber
GROUP BY ReceiptID
) T ON T.ReceiptID = ROL.ReceiptID
GROUP BY ROL.ReceiptID, ROL.PurchaseOrderID, T.JobTotalCost

) Q1 ON Q1.ReceiptID = ReceiptOrder.ReceiptID AND Q1.PurchaseOrderID =
ReceiptOrder.PurchaseOrderID
WHERE Q1.ReceiptID = '00000137'

Quote:> You haven't specified a correlation so your total column will actually
give
> the total for the entire table. Was that intended or was it supposed to be
> the total for each receiptid?

> SELECT receiptid, purchaseorderid, pototalcost,
>  (SELECT COALESCE(SUM(pototalcost), 0)
>   FROM ReceiptTest
>   WHERE receiptid = R.receiptid)
>  AS jobtotalcost
>  FROM ReceiptTest AS R

> Alternatively:

> SELECT R.receiptid, R.purchaseorderid, R.pototalcost,
>  COALESCE(T.jobtotalcost,0) AS jobtotalcost
>  FROM ReceiptTest AS R
>  JOIN
>  (SELECT receiptid, SUM(pototalcost) AS jobtotalcost
>   FROM ReceiptTest
>   GROUP BY receiptid) AS T
>  ON R.receiptid = T.receiptid

> This output is really a report and any reporting application can easily
> totals and subtotals to your reports without you having to define them in
a
> query.

> You can also use CUBE/ROLLUP to produce totals:

> SELECT receiptid, purchaseorderid, SUM(pototalcost)
>  FROM ReceiptTest
>  GROUP BY receiptid, purchaseorderid
>  WITH ROLLUP

> --
> David Portas
> SQL Server MVP
> --

This is a modified repost of a query I put to the newsgroup last week.
So far I haven't gotten an answer on this.  In a nutshell, I want my
report to look like this:

Salesperson   Sales
joe             10
mary            20
phil            15
----
45

I want the "45" to be positioned below the 15 (close to it, not at
the bottom of the page).  How do I accomplish this seemingly impossible
feat using Foxpro 2.5 for Windows Report Writer?

Larry

11. Totals Line