Count based on value (1 Viewer)

[Joy83]

Hi
I have sql statement
That shows the total number of students in a table
I want to add a condition to the count based another value in the table
Nationally: US or EU
So I just want to count those only

how to this in sql or in query design

thank you!

 

[Eugene-LS]

Use DCount() Function

 

[strive4peace]

hi @Joy83

an easy way to do this is to turn the query into a Group/Totals query. In the design view of the query, click the Totals button

this adds a row labeled "Total" to the query -- choose Group By if you want to group by a field (such as Nationally), or another choice to aggregate (such as Count on the primary key field), or "Where" if you just want that column to be for criteria

 

[Joy83]

Thanks for your reply
I tried to do it
But I am doing something wrong
This is my sql
I want US to show us count
And EU yo show Eu count
Even by using only US it’s not working

i don’t get any error
But empty results


SELECT students.nationality, Count(students.nationality)AS US, Count(students.nationality)AS EU
FROM students
GROUP BY students.nationality
HAVING students.nationality=DCount(“ID", “Students”, “nationality =‘US’”)
ORDER BY Count(students.nationality)AS US

 

[strive4peace]

hi @Joy83

try something like this:

SELECT students.nationality
, Sum(iif(nz(students.nationality,"")="US",1,0) as CountUS
, Sum(iif(nz(students.nationality,"")="EU",1,0) as CountEU,
FROM students
GROUP BY students.nationality

or

SELECT students.nationality
, count(ID) as HowMany,
FROM students
GROUP BY students.nationality

 

[Joy83]

It worked
Thaaanks!

 

[strive4peace]

you're welcome, @Joy83 ~ happy to help

 

[The_Doc_Man]

Just to save you some typing...

In the query you showed us, it was a single-table query (from table Students) so you do not need any qualifiying prefixes. This isn't exactly what Crystal showed you to do, but it will illustrate the point.

This:

SELECT students.nationality, Count(students.nationality)AS US, Count(students.nationality)AS EU
FROM students
GROUP BY students.nationality
HAVING students.nationality=DCount(“ID", “Students”, “nationality =‘US’”)
ORDER BY Count(students.nationality)AS US

can more easily be written as this:

SELECT nationality, Count(nationality)AS US, Count(nationality)AS EU
FROM students
GROUP BY nationality
HAVING nationality=DCount(“ID", “Students”, “nationality =‘US’”)
ORDER BY US

When you have only one record source in the FROM clause, you don't need to qualify the fields because there is no other place from which to get your data. This principle won't change the answer you got - but it might help you do less typing sometimes - which makes it less likely to make any typing errors when entering the queries. And if you DO have multiple sources in a JOIN query, then you can qualify their sources as needed.

Note also that the ORDER BY is potentially superfluous because you ALSO have a GROUP BY with the same field and no other fields.

 

[Pat Hartman]

1. HAVING applies to aggregated data. Non-aggregated data should be selected/eliminated using a WHERE clause. Access always assumes a having in a totals query unless you know enough to specify where. Having works on non-aggregated data but it is less efficient than using the WHERE to whittle down the recordset BEFORE aggregating it. If there are a million records and you want only the US and UK ones, why aggregate a million rows and THEN choose? Choose first with WHERE and you aggregate 100,000 rows instead and can use indexes assuming the country field is indexed so you don't actually have to even read the other 900,000 records to eliminate them.
2. Doc, I don't think your queries work. Count() counts the entire recordset not parts of it which is why Crystal used Sum()
3. And finally, in no case should domain functions be used inside of queries.

 

[strive4peace]

@Pat Hartman's point 3 should to be qualified to SELECT queries because sometimes you must use domain aggregate functions for UPDATE and APPEND ~

DSum, DCount, DMax, etc are domain aggregate functions since they aggregate data into one value

 

[Joy83]

1. HAVING applies to aggregated data. Non-aggregated data should be selected/eliminated using a WHERE clause. Access always assumes a having in a totals query unless you know enough to specify where. Having works on non-aggregated data but it is less efficient than using the WHERE to whittle down the recordset BEFORE aggregating it. If there are a million records and you want only the US and UK ones, why aggregate a million rows and THEN choose? Choose first with WHERE and you aggregate 100,000 rows instead and can use indexes assuming the country field is indexed so you don't actually have to even read the other 900,000 records to eliminate them.
2. Doc, I don't think your queries work. Count() counts the entire recordset not parts of it which is why Crystal used Sum()
3. And finally, in no case should domain functions be used inside of queries.

thanks you for the useful notes.

i used sum and it’s working fine for the time being
Do you suggest another way to do it?

also, I wanted to add to that sql a condition to say
If the “StartDate” is between two dates
I failed to do this with the current “sum” statement

 

[Pat Hartman]

You'll have to show us what you tried that "didn't work" and tell us why it didn't work.

Generally:
Where Something = Forms!yourform!Something AND SomeDate between Forms!Yourform!StartDate and Forms!Yourform!EndDate

 

[Pat Hartman]

Thanks Crystal. I didn't qualify #3 because it is poor practice to store calculated values but yes, they are an option if you decide you need to violate normal forms.

 

[Joy83]

You'll have to show us what you tried that "didn't work" and tell us why it didn't work.

Generally:
Where Something = Forms!yourform!Something AND SomeDate between Forms!Yourform!StartDate and Forms!Yourform!EndDate

SELECT stdate, students.nationality
, Sum(iif(nz(students.nationality,"")="US",1,0) as CountUS
, Sum(iif(nz(students.nationality,"")="EU",1,0) as CountEU,
FROM students
GROUP BY students.nationality
GROUP by stdate




When I put the stdate in the selection it forces me to put it in the aggregation as GROUP BY
And when I do this, the results I get are not aggregated by the nationality as I want
I see all records with the dates grouping

 

[Pat Hartman]

Replace the group by selection with WHERE and then put the between on the next line

 

[Joy83]

Replace the group by selection with WHERE and then put the between on the next line

I really appreciate your reply
But I am not sure I understand

 

[Pat Hartman]

When you choose WHERE, the Show box will also be unchecked. If it isn't, you need to do it.

 

[strive4peace]

GROUP BY students.nationality
GROUP by stdate

hi @Joy83

The GROUP BY keyword is only used once ... do this instead:

GROUP BY students.nationality, stdate

 

[Pat Hartman]

I think Joy is trying to add a where clause and the QBE is being extra "helpful".

I wanted to add to that sql a condition to say
If the “StartDate” is between two dates

 

[strive4peace]

@Joy83

if you want date criteria and the date range comes from unbound controls (so data type not known) on a form in a TEXTbox, you might have to wrap the date references with a function such as DateValue to convert from text to date

SELECT stdate, students.nationality
, Sum(iif(nz(students.nationality,"")="US",1,0) as CountUS
, Sum(iif(nz(students.nationality,"")="EU",1,0) as CountEU,
FROM students
WHERE stdate between DateValue(Forms!Yourform!StartDate) and DateValue(Forms!Yourform!EndDate)    
GROUP BY students.nationality

You wouldn't also group by the start date -- it would appear on the grid with the Total function being "Where", and the Show box wouldn't be checked ~