Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Grand Total ? 2

Status
Not open for further replies.
Johnny42

Johnny42

Technical User
Jul 13, 2004
127
CA
Realy new to SQL.
Mamaged to put this together :

SELECT ITEM, "DESC" , SUM (QTYSHIPPED) as "Total Units Sold" FROM "OEINVD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' ) group by "DESC" ,ITEM




How would I get a grand total of this query also ?
 
Sort by date Sort by votes
Code: 
SELECT ITEM
     , "DESC" 
     , SUM (QTYSHIPPED) 
              as "Total Units Sold"   
  FROM "OEINVD"  
 where ITEM 
    in ('80-5 -      -           '
       ,'80-5P-      -           '
       ,'80-6 -      -           '
       ,'80-6P-      -           '
       ,'80-7 -      -           '
       ,'80-7P-      -           ' ) 
group 
    by "DESC" 
     , ITEM
union all
SELECT null
     , 'Grand Total'
     , SUM (QTYSHIPPED)
  FROM "OEINVD"  
 where ITEM 
    in ('80-5 -      -           '
       ,'80-5P-      -           '
       ,'80-6 -      -           '
       ,'80-6P-      -           '
       ,'80-7 -      -           '
       ,'80-7P-      -           ' )

rudy
SQL Consulting
 
Upvote 0 Downvote
You may consider an UNION query:
SELECT ITEM, "DESC" , SUM (QTYSHIPPED) as "Total Units Sold" FROM "OEINVD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' ) group by "DESC" ,ITEM
UNION
SELECT 'Total', 'Total', SUM (QTYSHIPPED) FROM "OEINVD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' )

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
I replaced the "OEINVD" table with "OECRDD" of this query and now have the query for my credit notes...
Can both integrate in one query as net sales ? I.E. Sales less Credits ? ( OEINVD - OECRDD ) or will the query be too long ?
 
Upvote 0 Downvote
The join is on the ITEM column ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
And which sort of join ?
Each ITEM in OEINVD should be present in OECRDD and vice-versa ?
Which DBMS ? in fact how can you coerce null value ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
EX: OEINVD:
Item 1 35
Item 2 40
Item 3 25
total 100
OECRDD:
Item 1 10
Item 2 6
Item 4 5
total 21

net :
Item 1 25
Item 2 34
Item 3 25
Item 4 -5
total 79

 
Upvote 0 Downvote
Something like this ?
SELECT ITEM, SUM(Qty)
FROM (
SELECT ITEM, SUM(QTYSHIPPED) As Qty FROM OEINVD GROUP BY ITEM
UNION
SELECT ITEM, -1 * SUM(QTYSHIPPED) FROM OECRDD GROUP BY ITEM
)
GROUP BY ITEM

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Can I use all of this in one query to only reult in the net results ?
 
Upvote 0 Downvote
Yes, I think, provided you add the where clauses.
If the result is OK you may consider an union with a similar for the grand total.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
What have you so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
SELECT ITEM, SUM(Qty)
FROM (
SELECT ITEM, "DESC" , SUM (QTYSHIPPED) as "Qty" FROM "OEINVD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' ) group by "DESC" ,ITEM
UNION
SELECT 'Total', 'Total', SUM (QTYSHIPPED) FROM "OEINVD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' )


SELECT ITEM, "DESC" , -1*SUM (QTYSHIPPED) as "Qty" FROM "OECRDD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' ) group by "DESC" ,ITEM
UNION
SELECT 'Total', 'Total', SUM (QTYSHIPPED) FROM "OECRDD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' )
)
GROUP BY ITEM
 
Upvote 0 Downvote
SELECT ITEM, SUM(Qty)
FROM (
SELECT ITEM, "DESC" , SUM (QTYSHIPPED) as "Qty" FROM "OEINVD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' ) group by "DESC" ,ITEM
UNION
SELECT 'Total', 'Total', SUM (QTYSHIPPED) FROM "OEINVD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' )
[highlight]UNION[/highlight]
SELECT ITEM, "DESC" , -1*SUM (QTYSHIPPED) as "Qty" FROM "OECRDD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' ) group by "DESC" ,ITEM
UNION
SELECT 'Total', 'Total', [highlight]-1*[/highlight]SUM (QTYSHIPPED) FROM "OECRDD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' )
)
GROUP BY ITEM

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Tried...resulted in this error...

ODBC Error: SQLSTATE = 37000, Native error code = 0
Syntax Error: SELECT ITEM, SUM(Qty)
FROM (
SELECT<< ??? >> ITEM, "DESC" , SUM (QTYSHIPPED) as "Qty" FROM "OEINVD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P-
 
Upvote 0 Downvote
Derived Tables need an alias

SELECT ITEM, SUM(Qty)
FROM (
SELECT ITEM, "DESC" , SUM (QTYSHIPPED) as "Qty" FROM "OEINVD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' ) group by "DESC" ,ITEM
UNION
SELECT 'Total', 'Total', SUM (QTYSHIPPED) FROM "OEINVD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' )
UNION
SELECT ITEM, "DESC" , -1*SUM (QTYSHIPPED) as "Qty" FROM "OECRDD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' ) group by "DESC" ,ITEM
UNION
SELECT 'Total', 'Total', -1*SUM (QTYSHIPPED) FROM "OECRDD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P- - ','80-7 - - ','80-7P- - ' )
) A
GROUP BY ITEM

Questions about posting. See faq183-874
 
Upvote 0 Downvote
Tried , still ended up with this :

ODBC Error: SQLSTATE = 37000, Native error code = 0
Syntax Error: SELECT ITEM, SUM(Qty)
FROM (
SELECT<< ??? >> ITEM, "DESC" , SUM (QTYSHIPPED) as "Qty" FROM "OEINVD" where ITEM in('80-5 - - ','80-5P- - ','80-6 - - ','80-6P-
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

tekdudedude
  • Locked
  • Question
Getting a Total of Values IN() 1
Replies
2
Views
77
tekdudedude
tekdudedude
tekdudedude
  • Locked
  • Question
Getting Total Records When Child Table Has a Value 1
Replies
4
Views
58
tekdudedude
tekdudedude
russland
  • Locked
  • Question
query result and it's total 1
Replies
1
Views
44
PHV
PHV
bubbisthedog
  • Locked
  • Question
Calculating Percent of Total... 1
Replies
2
Views
36
bubbisthedog
bubbisthedog
dcjeans
  • Locked
  • Question
Possible to get a total sum of results from a query?
Replies
5
Views
43
dcjeans
dcjeans

Part and Inventory Search

Sponsor

Back
Top