Possible to get a total sum of results from a query?

[dcjeans]

I have a query for an inventory catalog that looks like this.

select RIGHT(C.barcode, 12), C.description, C.size, (select sum(stock_count) from count_update where barcode = RIGHT(C.barcode, 12)) TotalCount, (select stock_count from count_update where barcode = RIGHT(C.barcode,12))-(select inventory from current_inv where barcode = C.barcode) Variance, ((select stock_count from count_update where barcode = RIGHT(C.barcode,12))-(select inventory from current_inv where barcode = C.barcode))*(select cost from current_inv where barcode = C.barcode) MoneyVar
from current_inv c
ORDER BY (select _rowid from print_inv_sheets where barcode = C.barcode);

Which returns barcode numbers, item description and size, variance for each item from the last reconciliation, and how much each variance costs.

What i would like to do is also have a total sum of all count variances and money variances for all items in the catalog.

Any suggestions?

Thanks in advance.

Darron

 

[rac2]

Use a GROUP BY query for summing.

select RIGHT(C.barcode, 12),
       C.description,
       C.size,
      SUM( U.stock_count) TotalCount,
      SUM( U.stock_count - C.inventory) Variance,
      SUM( (U.stock_count - C.inventory) * C.cost ) MoneyVar 
FROM current_inv C 
JOIN count_update U ON U.barcode = RIGHT(C.barcode, 12)
JOIN print_inv_sheets P ON P.barcode = C.barcode

GROUP BY RIGHT(C.barcode, 12), P._rowid
       C.description,
       C.size

ORDER BY P._rowid from print_inv_sheets

Also use JOINs instead of the subqueries.


Assuming there is a previous inventory for every item. Otherwise use LEFT JOIN and ISNULL(C.inventory,0) to handle items which do not have a previous inventoy.

And assuming every barcode corresponds to one _rowid.

 

[dcjeans]

Thanks for the reply.

I tried your query (copy / pasted) and got this error

SQL ERROR - [position 162, near '(' in ' SUM((U.stock_count '] function label expected.

I am a complete newbie at SQL, so I am unsure how to fix. It appears to me that there is a function label (TotalCount) so I am not sure why this is returning an error.

Any ideas?

Darron

 

[rac2]

I dont see it. If you used copy/paste then it must be my mistake unless the copy/paste picked up some cf/lf which your SQL processor is interpreting as a delimiter. Try this syntax.


Also the error is probably for the MoneyVar expression since it shows two parentheses following the SUM.

select RIGHT(C.barcode, 12),
       C.description,
       C.size,
      SUM( U.stock_count) AS "TotalCount",
      SUM( U.stock_count - C.inventory ) AS "Variance",
      SUM( (U.stock_count - C.inventory) * C.cost ) AS  "MoneyVar" 
FROM current_inv C 
JOIN count_update U ON U.barcode = RIGHT(C.barcode, 12)
JOIN print_inv_sheets P ON P.barcode = C.barcode

GROUP BY RIGHT(C.barcode, 12), P._rowid,
       C.description,
       C.size

ORDER BY P._rowid from print_inv_sheets

Oh now I see. There was a comma missing after P._rowid .

 

[dcjeans]

Strange,

Copy/pasted the new syntax, same error, well almost the same, position is different this time.

SQL ERROR - [position 175, near '(' in ' SUM( (U.stock_count'] function label expected.

I am using Openbase, if that makes a difference.

Darron

 

[dcjeans]

I tried changing that line of the query just to try to get past it to this:
select RIGHT(C.barcode, 12),
C.description,
C.size,
SUM( U.stock_count) AS "TotalCount",
SUM( U.stock_count - C.inventory ) AS "Variance",
SUM(U.stock_count - C.inventory * C.cost ) AS "MoneyVar"
FROM current_inv C
JOIN count_update U ON U.barcode = RIGHT(C.barcode, 12)
JOIN print_inv_sheets P ON P.barcode = C.barcode

GROUP BY RIGHT(C.barcode, 12), P._rowid,
C.description,
C.size

ORDER BY P._rowid from print_inv_sheets

I didn't get an error at that point this time, but I did get another error. This time it returned
SQL ERROR - [position 248, near 'JOIN' in 'rent_inv C JOIN count_up'] comma expected but not found.

Darron