March 12, 2018 at 7:43 pm
Basically, I got 1 table working with 2 rows on it. Now I want
to add
3rd row wherein I want to SUM a field in the first row and
SUM a field
of
2nd row and have the two SUM results appear in the third row,
just as
a query (not create a new table). I tried UNION but this gives
an
error.
I want a TOTAL of TOTAL EX GST
SELECT TOP (100) PERCENT CONVERT(varchar, DATEADD(dd, - 1,
GETDATE()), 103) AS Date, 'Sales Orders' AS Type,
COUNT(dbo.SalesOrder.SalesOrderID) AS Orders,
SUM(dbo.SalesOrderDetails.QtyOrdered) AS Chairs,
ISNULL(ROUND(SUM(dbo.SalesOrderDetails.ExtendedPrice),
2), 0) AS [Total Ex GST]
FROM dbo.SalesOrder LEFT OUTER JOIN
dbo.SalesOrderDetails ON dbo.SalesOrder.SalesOrderID =
dbo.SalesOrderDetails.SalesOrderID
WHERE (dbo.SalesOrder.EntryDate >= CONVERT(char(8),
DATEADD(dd, - 1, GETDATE()), 112)) AND (dbo.SalesOrder.EntryDate <
CONVERT(char(8), GETDATE(), 112)) AND (dbo.SalesOrder.CustomerID
<> 187)
UNION ALL
SELECT TOP (100) PERCENT CONVERT(varchar, DATEADD(dd, - 1,
GETDATE()), 103) AS Date, 'Invoices w/o SO' AS Type,
COUNT(dbo.Invoice.InvoiceID) AS Orders, COUNT(dbo.Invoice.InvoiceID) AS
Chairs,
ISNULL(ROUND(SUM(dbo.InvoiceDetails.ExtendedPrice),
2), 0) AS [Total Ex GST]
FROM dbo.Invoice INNER JOIN
dbo.Customers ON dbo.Invoice.CustomerID =
dbo.Customers.CustomerID INNER JOIN
dbo.InvoiceDetails ON dbo.Invoice.InvoiceID =
dbo.InvoiceDetails.InvoiceID
WHERE (dbo.InvoiceDetails.ItemNo = '_Special') AND
(dbo.Invoice.CredInvoiceNo IS NULL) AND (dbo.Invoice.EntryDate >=
CONVERT(char(8), DATEADD(dd, - 1, GETDATE()), 112)) AND
(dbo.Invoice.EntryDate < CONVERT(char(8),
GETDATE(), 112)) AND (dbo.Customers.CustomerID <> 187)
If I have the view of my table
Date Type Orders TOTAL
13/03 Sales Order 300 15000
13/03 Invoice 200 10000
-------------------------------- 25000 (How can I get this sum
of 25000) as a field?
Then I want the result to be 25000`enter code here`
I would be happy to exclude the t_stamp for now just so I can get the SUM's. Please assist
March 12, 2018 at 10:12 pm
Like this?
SELECT YrMo
, OType
, Orders
, Total
FROM
(SELECT '13/03'AS YrMo
, 'Sales Order' AS OType
, 300 AS Orders
, 15000 AS Total
UNION ALL
SELECT '13/03'
, 'Invoice'
, 200
, 10000 ) x
UNION
SELECT NULL, NULL, NULL, SUM(Total)
FROM
(SELECT '13/03'AS YrMo
, 'Sales Order' AS OType
, 300 AS Orders
, 15000 AS Total
UNION ALL
SELECT '13/03'
, 'Invoice'
, 200
, 10000 ) y
March 13, 2018 at 11:09 am
Use GROUPING SETS. The example is based on Piet's sample data since you didn't provide your own sample data. It will be more efficient if you use the grouping sets in your initial aggregation instead of doing a second aggregation to get the grand total.
SELECT YrMo
, CASE WHEN GROUPING(OType) = 1 THEN 'Grand Total' ELSE OType END AS OType
, SUM(Orders) AS Orders
, SUM(Total) AS Total
FROM
(SELECT '13/03'AS YrMo
, 'Sales Order' AS OType
, 300 AS Orders
, 15000 AS Total
UNION ALL
SELECT '13/03'
, 'Invoice'
, 200
, 10000 ) x
GROUP BY GROUPING SETS((YrMo, OType), ())
ORDER BY GROUPING(OType), OType DESC
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply