Querying the number of specific items in a database
I was just wondering how could I count the number of a specific equipment..
SELECT EQUIPMENTS.DESCRIPTION AS [EQUIPMENT TYPE], Count(EQUIPMENTS.EQNAME) AS QUANTITY, (SELECT Count(EQUIPMENTS.CONDITION) FROM EQUIPMENTS WHERE EQUIPMENTS.CONDITION = 'Functional') AS WORKING, (SELECT Count(EQUIPMENTS.CONDITION) FROM EQUIPMENTS WHERE EQUIPMENTS.CONDITION = 'Non-Functional') AS [NON-WORKING] FROM EQUIPMENTS GROUP BY EQUIPMENTS.DESCRIPTION;
this query returns the following :
EQUIPMENT NAME : PROJECTOR QUANTITY : 3 WORKING : 2 NON-WORKING :1
Now if I add another equipment which has a different type, for example CALCULATOR, it would have the same count of WORKING AND NON-WORKING which only is for the PROJECTOR. How do I Make it such that it also counts the quantity of the Calculator and the number of working and non-working itself? I mean whenever I add another equipment which has a specific description, the query would also count it independently?
I'm using VB.NET and this query is made in MS ACCESS 2007.
Use IIf() expressions to return 1 when the condition is satisfied, and 0 when not. Then Sum those values.
SELECT e.DESCRIPTION AS [EQUIPMENT TYPE], Count(e.EQNAME) AS QUANTITY, Sum(IIf(e.CONDITION = 'Functional', 1, 0)) AS WORKING, Sum(IIf(e.CONDITION = 'Non-Functional', 1, 0)) AS [NON-WORKING] FROM EQUIPMENTS AS e GROUP BY e.DESCRIPTION;