Count in a Query 2

[netrusher]

I have a query that I want to get some totals on in a certain date range.

The fields in the query are:
SystemGroup
Problem
FaultCount
Date

Under Fault Count there is a possibility of two answers: Cosmetic or Mechanical

What I would like to do in the query is after I run the Query between two dates
I would like to see what my two totals are for Cosmetic Faults and Mechanical Faults.

I thought I had an expression that would work but it will not work. I am stumped.

So bottom line: after I run the query I would like to see totals for Cosmetic Faults and Mechanical Faults.

Thanks in advance

 

[Golom]

The usual is

Select FaultCount, Count(*) As [Number of Faults]

From myTable

Where [Date] BETWEEN #07/01/2006# And #07/31/2006#

Group By FaultCount

assuming that the field "FaultCount" holds the values "Cosmetic" and "Mechanical".

You can of course plug in parameters where I have shown hard-coded dates.

 

[netrusher]

I entered the above into the SQL for the Query. I changed the dates and I am getting a total of 10 faults which is correct but I really should see that there is a total of 4 Cosmetic Faults and 6 Mechanical Faults.

Am I doing something wrong?

 

[netrusher]

Ultimately I would like three different outputs for the same query.

Total Faults 10
Cosmetic 4
Mechanical 6

That is what my totals would be now.

 

[Golom]

You will need a union for that

Select FaultCount, Count(*) As [Number of Faults]
From myTable
Where [Date] BETWEEN #07/01/2006# And #07/31/2006#
Group By FaultCount

UNION ALL

Select 'Total Faults', Count(*)
From myTable
Where [Date] BETWEEN #07/01/2006# And #07/31/2006#

If you entered it as written then it should have returned
[tt]
Cosmetic 4
Mechanical 6
[/tt]
Didn't it?

 

[netrusher]

I just now put this in the SQL for the Query as below:

It asks me for a FaultCount Parameter.
Then I get a total of 10 & 10
Cosmetic does not show anywhere
Mechanical does not show anywhere.

Thanks for any help. I also would like a total of 10 to appear.

Select FaultCount, Count(*) As [Number of Faults]
From WorkUnitsFaultsMainTBL
Where [Date] BETWEEN #08/01/2006# And #08/31/2006#
Group By FaultCount

UNION ALL

Select 'Total Faults', Count(*)
From WorkUnitsFaultsMainTBL
Where [Date] BETWEEN #08/01/2006# And #08/31/2006#

 

[netrusher]

What is the Union All? and where am I getting Total Faults from?

 

[lespaul]

what you are saying doesn't make any sense. If there is a field named FaultCount in the table, you should get exactly what you asked for. Here's an explanation of how the aggregate function and GROUP BY clause work:
[tt]
An aggregate function performs based on all the other fields in your select.

If you have a table like:

Salesman SaleDate Amount
JR 1/1/2006 $500.00
EM 1/1/2006 $250.00
JR 1/1/2006 $100.00
EM 1/2/2006 $101.00
JR 1/2/2006 $75.00
JR 1/2/2006 $100.00

and you want to sum the sales for the month for EACH salesman:

SELECT Salesman, SUM(Amount) FROM TableName GROUP BY Salesman

the results would be:

JR $775.00
EM $351.00

If you want to know how much per day:

SELECT SalesDate, SUM(Amount) FROM TableNmae GROUP BY SalesDate

the results would be:

1/1/2006 850.00
1/2/2006 276.00

If you want by Salesman, by Date:

SELECT Salesman, SalesDate, SUM(Amount) FROM tableName GROUP BY Salesman, SalesDate ORDER BY Salesman, SalesDate

EM 1/1/2006 250.00
EM 1/2/2006 101.00
JR 1/1/2006 600.00
JR 1/2/2006 175.00

So, yes, by adding additional fields to your select, you will have different SUMs.

Can you see what the "GROUP BY" is doing now? It's GROUPING together like information and performing the aggregate function in the SELECT on that GROUP.[/tt]

if there is a field named FaultCount and it has either 'COSMETIC' or 'MECHANICAL' in the field, by running a query:

SELECT FaultCount, count(*) FROM tableName GROUP BY FaultCount

you should get the requested results.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[netrusher]

Leslie,

Thanks very much. The FaultCount field was really FaultCategory. Once I changed that it works just fine and I appreciate your help. Sorry for the mistake.

Allen