Get column total on same line

Get column total on same line

Post by Steve Beac » Tue, 27 Jan 2004 05:48:38



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

Post by Joe Celk » Tue, 27 Jan 2004 07:03:51


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

Post by David Porta » Tue, 27 Jan 2004 09:39:01


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

Post by Steve Beac » Wed, 28 Jan 2004 10:26:43


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

Thanks for your help.

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

 
 
 

1. Need help getting totals under columns in Report Writer

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

2. Archiving Redo logs while tablespace is in backup mode.

3. Getting rid of the IDENTITY property on a column withou getting rid of the column

4. MicroStrategy - Feedback on Usage

5. Access/English Challenge Question: Calculate Totals rather than Total Totals in English/Access

6. create a referzintegritity

7. Formatting a Total Line

8. US-NC-Raleigh/Durham Oracle DBAs Needed..

9. Total lines of a text file

10. computing percentages on break total lines

11. Totals Line

12. total details max 10 datail lines in table

13. calculated line totals in DBGrid?