subtotal in query

[T.S.Negi]

Hi,

WITH SQL SERVER QUETY, I have output like

PRJ item qty
=============== ==
P1 I1 10
P1 I1 20
P1 I2 10
P2 I2 10
P2 I3 10
=============== =

I WANT TO MANIPULATE IT LIKE THIS

PRJ item qty
=============== ==
P1 I1 30
P1 I2 10
---------------
P1 * 40
---------------
P2 I2 10
P2 I3 10
---------------
P2 * 20
---------------
===============
PRJ * 60
===============

SHOW DATA AND SUBTOTAL AND GRAND TOTAL IN ONE RESULTSET


CAN ANYBODY HELP

THANKS IN ADV.

T.S.NEGI

[David Portas]

Re: subtotal in query

> WITH SQL SERVER QUETY, I have output like

It helps if you can post DDL for the base tables rather than the output of a
query. The answer to your question might be different depending on factors
such as keys, constraints and nullability in your table(s). For this example
I'll assume that the sample data you posted exists as a table and that none
of the columns is nullable.

SQLServer's ROLLUP feature allows you to add totals and subtotals:

SELECT prj, item, SUM(qty) AS qty
FROM Sometable
GROUP BY prj, item
WITH ROLLUP
ORDER BY GROUPING(prj), prj, GROUPING(item), item

Or you can achieve the same result using standard SQL:

SELECT prj, item, qty
FROM
(SELECT prj, item, SUM(qty) AS qty
FROM Sometable
GROUP BY prj, item
UNION ALL
SELECT prj, NULL, SUM(qty)
FROM Sometable
GROUP BY prj
UNION ALL
SELECT NULL, NULL, SUM(qty)
FROM Sometable) AS X
ORDER BY COALESCE(prj,'Z '), prj, COALESCE(item,' Z'), item

--
David Portas
------------
Please reply only to the newsgroup
--

[--CELKO--]

Re: subtotal in query

>> SHOW DATA AND SUBTOTAL AND GRAND TOTAL IN ONE RESULTSET <<

That is a report and not a query. You did not post any DDL and the
sample data you showed us is not a table -- it has no keys. You need
to learn SQL before you try to use it; you thinking that you are still
doing sequential file processing and classic 1950's "control break
reports" like we did with magneitc tape and punch card systems.

Oh, if you don't want to be a good programmer, you caqn kludge it with
a UNION.