0

I want to show the total qty of these query

(total qty from datagridview where ActualQty value will update the values of Used Column)
SELECT COUNT(Software) AS ActualQty FROM tblswlogs WHERE Software='MS EXCEL'

and

(Total will appear in textbox)
SELECT MAX(used + initialqty) As Total FROM tblswlist WHERE Software = 'MS EXCEL'

Attachments Actual_Qty.PNG 4.21 KB
2
Contributors
1
Reply
24
Views
2 Years
Discussion Span
Last Post by mcglk
0

I have to admit, I’m not clear on the question. Among other things, you don’t mention which version of MySQL you’re running (I can only assume it’s MySQL from the tag); putting more details about the environment is always useful.

Also, while I’m assuming the first query (SELECT COUNT(Software) …) gives you the number of copies that are currently in use (in the attachment you’ve given, for the “MS EXCEL” row, it would be the value “3”), the second query—what’s that about? Is that where you’re coming up with the “2500” value in the same row? Because used + initialqty to come up with the total seems odd to me, and wanting the sum of the two queries seems even stranger.

But let’s assume these two queries are accurate. In that case, you’ll need a couple of subselects, each of which will have a table alias, with columns you can select for:

SELECT
    (t1.actualqty + t2.total) AS "Grand total"
  FROM
    (SELECT
         `software`,
         COUNT(`software`) AS actualqty
       FROM `tblswlogs`
       GROUP BY `software`
    ) AS t1,
    (SELECT
         `software`,
         MAX(`used` + `initialqty`) AS total
       FROM `tblswlist`
       GROUP BY `software`
    ) AS t2
  WHERE t1.software='MS EXCEL'
    AND t2.software='MS EXCEL';

Does that help? (Note: I’ve only tested this under MySQL 5.6.)

Edited by mcglk: Note on testing environment.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.