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 Availab开发者_运维百科le
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!
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 )
精彩评论