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.

Answers


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;

Need Your Help

How do I insert a POST request into a UIWebView

iphone post

For a GET request I've tried this simple method:

specify list of floats in argparse

python input argparse

Using argparse, I would like the user to specify 6 values as the argument of one optional input argument. Is there a built-in way to check if those values are floats?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.