Tutorial :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!


Solution: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 )  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »