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 Aggregate Query with multiple Criteria

P: 2
My table consists of DeviceID, Identification_Number, Amt.

A query needed which will display the count of Amt=0 and count of Amt>0 and Amt<10, Count of Amt>10 and Amt<100, Count of Amt>100 and Amt<1000.
1 Week Ago #1
Share this Question
Share on Google+
1 Reply

Expert Mod 2.5K+
P: 3,158
See your earlier post concerning your fortunate-ness in my response. Again, I assume you have no idea where to start.

What you are looking for is called a "CrossTab Query, which is similar to a Pivot Table in Excel. It aggregates values across various fields in your Table and can categorize based upon the criteria your provide. I can't image that you would have stumbled upon this solution all by yourself if you are new to SQL. Here is what I have that may suit your needs:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(TableName.DeviceID) AS CountOfDeviceID 
  2. SELECT "Device Count" AS DeviceCount 
  3. FROM TableName 
  4. GROUP BY "Device Count" 
  5. PIVOT IIf([Amt]=0,"0",IIf([Amt]>0 And [Amt]<=10,"1-10",IIf([Amt]>10 And [Amt]<=100,"11-100",IIf([Amt]>100 And [Amt]<=1000,"101-1000",">1000"))));
You will have to replace "TableName" with the name of your table.

The key in this query is the expression after the "PIVOT" Statement. This splits up the values of your table based upon the Amt value in your table, provides a "heading" for that value and then counts hte number of entries.

Hope this hepps!
1 Week Ago #2

Post your reply

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