By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,831 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,831 IT Pros & Developers. It's quick & easy.

Grouping Query by Device ID with Criteria

P: 2
A Table consists of DeviceID, Identification_number, Amt.

Need a query which will display the count of records where Amt is more than 0 and group by DeviceID.

Please help.
1 Week Ago #1
Share this Question
Share on Google+
2 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,158
subhankarmc,

Welcome to Bytes!

Consider yourself fortunate that this post receives any attention, as the information you provided is very limited, and it does not appear that you have tried anything that we can actually troubleshoot, but you just want a solution.

I am going to make an assumption that your knowledge of SQL is very limited, and hence, you have no idea where to start, so I will try to explain what you need to do.

What you are asking for is a simple "Aggregate Query" in which you group certain fields and perform calculations across the table. In your case, you also have criteria that limit the scope of your query. So, what you want is this:

Expand|Select|Wrap|Line Numbers
  1. SELECT DeviceID, Count(Identification_number) AS Devices 
  2. FROM [YourTableName] 
  3. WHERE Amt > 0 
  4. GROUP BY DeviceID;
This should get you headed in the right direction. Let us know if you run into snags.

Hope this hepps!
1 Week Ago #2

NeoPa
Expert Mod 15k+
P: 31,302
I would just warn that counting records is best done using Count(*), rather than Count([Field]).

The former is generally recognised as referring to the count of the records per se, but the latter is less clear as well as being open to errors. If the field selected is not a [Required] field then records where that field is not populated (Null) will not be counted, thus giving an inaccurate result.
1 Week Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.