3

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.

Matt Dodge
  • 10,833
  • 7
  • 38
  • 58
Break the Law
  • 217
  • 2
  • 14

1 Answers1

1

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;
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • I have a question, as I was transferring this query to the TableAdapter Query Configuration Wizard in VB.Net, there appears a notice. Error in list of function arguments: '=' not recognized. Unable to parse query text what seems to be the problem? – Break the Law Jan 03 '13 at 15:04
  • Sorry I don't know VB.Net, so I've no clue why it would complain about that SQL. – HansUp Jan 03 '13 at 15:27