How can I get multiple counts with one SQL query?

by

You can use a CASE statement with an aggregate function. This is basically the same thing as a PIVOT function in some RDBMS:

SELECT distributor_id,
    count(*) AS total,
    sum(case when level="exec" then 1 else 0 end) AS ExecCount,
    sum(case when level="personal" then 1 else 0 end) AS PersonalCount
FROM yourtable
GROUP BY distributor_id

More Related Contents:

  1. Creating stored procedure getting Incorrect syntax near the keyword ‘Declare’
  2. How to create id with AUTO_INCREMENT on Oracle?
  3. Conversion failed when converting date and/or time from character string while inserting datetime
  4. Difference between EXISTS and IN in SQL?
  5. How should I pass a table name into a stored proc?
  6. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  7. SQL Join Differences
  8. Get the last inserted row ID (with SQL statement) [duplicate]
  9. Get the week start date and week end date from week number
  10. Is it possible to make a recursive SQL query?
  11. Fetch the rows which have the Max value for a column for each distinct value of another column
  12. Run all SQL files in a directory
  13. Sql Server deterministic user-defined function
  14. How to get the numeric part from a string using T-SQL?
  15. MySQL: How to copy rows, but change a few fields?
  16. How to renumber primary index
  17. Query that ignore the spaces
  18. How to enable Ad Hoc Distributed Queries
  19. Sleep function in ORACLE
  20. Postgres function returning table not returning data in columns
  21. SQL query to select distinct row with minimum value
  22. SQL Server SELECT LAST N Rows
  23. update columns values with column of another table based on condition [duplicate]
  24. how to select even records from a table in oracle?
  25. Find all records which have a count of an association greater than zero
  26. How to get rid of “Error 1329: No data – zero rows fetched, selected, or processed”
  27. Difference between View and table in sql
  28. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  29. SQL: Aggregating strings together
  30. Flattening of a 1 row table into a key-value pair table

Leave a Comment