Goods Issue - SQL Query will not sum

[Former Member]

I have 3 Goods Issue documents with Document Total for each document 10,000 / 20,000 / 30,000 subsequently.

I want to make query so that it will display like below - where it shows BOTH each of 3 document value (10000/2000/30000) AND the sum of the 3 documents (60000) like below.

Doc 1 - 10,000

Doc 2 - 20,000

Doc 3 - 30,000

-

---

Total = 60,000

-

---

In addition, I would like the ability to choose date range. Basically, something like

SELECT Document_Total

FROM Goods_Issue table

WHERE the_document_date is between 1-SEP-2011 and 31-SEP-2011

AND the_reference_is _________________

I have tried many SQL queries, but it displayed either:

Doc 1 - 10,000

Doc 2 - 20,000

Doc 3 - 30,000

OR

Total = 60,000

Please help.

[Former Member · Accepted solution]

Hi,

SELECT T0.DocNum, SUM(T0.DocTotal) FROM OIGE T0
WHERE T0.DocDate >=[%0] AND T0.DocDate <=[%1]
GROUP BY T0.DocNum WITH ROLLUP

Best Regards,

Hendry Wijaya

[former_member218051]

Hi ,

Try This

SELECT convert(varchar(10),t0.docnum) , t0.doctotal from oige t0 where t0.docdate >= '01/apr/2011'

union all

select 'Total' , isnull(sum(t0.doctotal),0) from oige t0 where t0.docdate >= '8/apr/2011'

Thanking you

Malhaar

[Former Member]

@Hendry Wijaya @GordonDu @malhaar

Thanks for helps. The SQL query you provided solved 99% of the problem. I just need to make a tweak on the SQL so that it could display the total on the footer. See screenshot below - I uploaded the pics at imageshack as I don't find a way to attach pics in here.

[See here - Total_at_Footer|http://i129.photobucket.com/albums/p213/whitesnowbear/AAAA/Untitled-2.jpg]

Thanks a bunch.

[former_member196081]

Hi,

press 'Ctrl' key and double click on the doctotal column. it will show you the sum at the footer.

or

You can design a layout for this.

Regards

Deepak

[Former Member]

This can bee done by Ctrl+Click. You can take out the SUM part of query. The word Total will not be showed.

[former_member325312]

Hi find the query below i think this will solve ur prob

SELECT T0.DocNum,SUM(T0.DocTotal) FROM OIGE T0

WHERE T0.DocDate >='2011/09/10' AND T0.DocDate <='2011/09/12'

GROUP BY T0.DocNum

Regards

Jennifer

[Former Member]

Hi,

Try:

SELECT T0.DocNum, T0.DocTotal FROM OIGE T0
WHERE T0.DocDate >=[%0] AND T0.DocDate <=[%1]
UNION ALL
SELECT '',SUM(IsNull(T0.DocTotal,0)) FROM OIGE T0
WHERE T0.DocDate >=[%0] AND T0.DocDate <=[%1]

Thanks,

Gordon