Combine rows of SQL Union query

Question

Hi,

I am trying to perform a SQL query which generates a table with two columns referencing the same data but using different conditionals. My result query needs to contain columns like:

Query Result:

• Total Quantity
• Available Quantity

Where Total Quantity is the total number of a certain item and Available is a subset of the same item, but only those which are flagged as "Available".

I've attempted to use a UNION of the form:

SELECT
   '0' as Quantity,
   COUNT (item.pkid) as Available
FROM itemInstance, itemType
WHERE
   itemInstance.availability = 'AVAILABLE'
   AND itemInstance.type = itemType.primary_key
UNION
SELECT
   COUNT (DISTINCT item.pkid) as Quantity,
   '0' as Available
FROM itemInstance, itemType
WHERE itemInstance.type = itemType.primary_key

Which of course produces something like:

  Quantity  Available
1    0          2
2    4          0
3    0          3
4    7          0

When I really want:

  Quantity  Available
1    4          2
2    7          3

Any help would be appreciated. Thanks!

Answer 1

I don't think I understand all aspects of your query, but it should basically work like this:

SELECT
   COUNT(*) as Quantity,
   SUM(CASE WHEN itemInstance.availability = 'AVAILABLE'
            THEN 1
            ELSE 0
            END) As Available
FROM itemInstance, itemType
WHERE itemInstance.type = itemType.primary_key

The CASE will be 1 for each matchin row and 0 for not-matchin rows, so the sum of those values is the count of available items.

BTW: You might want to switch to explicit join notation for better readablity (especially with more complex queries):

...
FROM itemInstance
JOIN itemType ON ( itemInstance.type = itemType.primary_key )