COUNT MANY FIELDS IN ACCESS [closed]

by

I assume that you want to group on the NameId and ClassId fields, and that the Sex field in the result is not sex at all, but the number of persons.

Something like this:

select
  NameId,
  ClassId,
  count(*) as Sex,
  sum(Sex) as Male,
  count(*) - sum(Sex) as Female
from
  TheTable
group by
  NameId,
  ClassId

Edit:

For Sex values 1 and 2 you can use a case to count them:

  sum(case Sex when 1 then 1 else 0 end) as Male,
  sum(case Sex when 2 then 1 else 0 end) as Female

More Related Contents:

  1. How to update only one field using Entity Framework?
  2. SQLite – UPSERT *not* INSERT or REPLACE
  3. Best way to do multi-row insert in Oracle?
  4. Why does Oracle 9i treat an empty string as NULL?
  5. Unioning two tables with different number of columns
  6. How do I use variables in Oracle SQL Developer?
  7. Fastest way to count exact number of rows in a very large table?
  8. Formatting Numbers by padding with leading zeros in SQL Server
  9. FULL OUTER JOIN with SQLite
  10. Importance of varchar length in MySQL table
  11. SQL SELECT speed int vs varchar
  12. Postgres Case Sensitivity
  13. How do I query for all dates greater than a certain date in SQL Server?
  14. Define variable to use with IN operator (T-SQL)
  15. Drop all tables command
  16. Sql script to find invalid email addresses
  17. ORA 00904 Error:Invalid Identifier
  18. can we list all tables in msaccess database using sql?
  19. What does the colon sign “:” do in a SQL query?
  20. Syntax of for-loop in SQL Server
  21. Why does ISNUMERIC(‘.’) return 1?
  22. Unfindable SQLite Syntax Error
  23. Comparison of Relational Databases and Graph Databases
  24. The difference between ‘AND’ and ‘&&’ in SQL
  25. Converting String List into Int List in SQL
  26. SELECT max(x) is returning null; how can I make it return 0?
  27. How to return default value from SQL query
  28. Is this a good way to model address information in a relational database?
  29. Conditional aggregation performance
  30. Retrieve Oracle last inserted IDENTITY

Leave a Comment