Combine rows of SQL Union query

Question

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!

Peter Lang · Accepted Answer · 2010-02-17 18:08:38Z

5

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 )

edited Feb 17, 2010 at 18:08

answered Feb 17, 2010 at 17:58

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HLGEM Over a year ago

Good on you asking him to use explicit joins. Implicit joins are such a poor practice.

Collectives™ on Stack Overflow

Combine rows of SQL Union query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related