How to Use Count and Group by At the Same Select Statement

How to use count and group by at the same select statement

This will do what you want (list of towns, with the number of users in each):

select town, count(town) 
from user
group by town

You can use most aggregate functions when using GROUP BY:

(COUNT, MAX, COUNT DISTINCT etc.)

Update (following change to question and comments)

You can declare a variable for the number of users and set it to the number of users then select with that.

DECLARE @numOfUsers INT
SET @numOfUsers = SELECT COUNT(*) FROM user

SELECT DISTINCT town, @numOfUsers
FROM user

How to use COUNT, Group by and BETWEEN in the same Query?

You may try this. where clause will come before group by.

You may find this link for more info.Group by.

  ;WITH CTE AS (
  SELECT ID, Name,Date FROM Records  
  WHERE Date BETWEEN '" +Start_Date+ "' AND '" +End_Date+ "' )
  SELECT COUNT(ID) AS COUNT_ID, NAME FROM CTE
  GROUP BY Name ORDER BY COUNT(ID) DESC;

or you can write it as

  SELECT COUNT(ID) AS COUNT_ID, NAME FROM (
  SELECT ID, Name,Date FROM Records  
  WHERE Date BETWEEN '" +Start_Date+ "' AND '" +End_Date+ "' ) AS D
  GROUP BY Name ORDER BY COUNT(ID) DESC;

How to query count() with different grouping from same table?

Just use SUM with a condition

SELECT 
  REGION
  ,SUM(CASE WHEN IS_SERVICED = 'TRUE' then 1 else 0 END) as TRUE_COUNT
  ,SUM(CASE WHEN IS_SERVICED = 'FALSE' then 1 else 0 END) as FALSE_COUNT 
FROM TABLE1
GROUP BY REGION

Using case, count, and group by in 1 query

When using Group By, the SELECT statement may only contain fields in the Group By statement or aggregate functions (like SUM or COUNT).

You can use a Common Table Expression (CTE) to consolidate the logic of building the CardStatus into a select statement, and then funnel that into a new query which leverages it in both the group by and select statements

;WITH BalanceCards AS (
    SELECT a.balance,
           (CASE WHEN c.status='Active'                THEN 'Active'
                 WHEN c.status IN ('Inactive','Other') THEN 'Inact'
                 WHEN c.status IS NULL                 THEN 'No Card' 
            END) AS CardStatus
    FROM people p
    JOIN accounts a ON p.id = a.PeopleFK
    LEFT JOIN cards c ON p.id = c.PeopleFK
)
SELECT bs.CardStatus, 
       SUM(bs.balance) As Total, 
       COUNT(*) As [Count]
FROM BalanceCards bs
GROUP BY bs.CardStatus

SELECT number of groups resulted from a GROUP BY query

SELECT count(*)
FROM (
        SELECT 1 as dummy
        FROM MyTable
        WHERE Col2 = 'x'
        GROUP BY Col1
     ) dt

No need to count rows in the sub-query, the result will be the same anyway.

COUNT and GROUP BY several columns

It's not clear why you need to join the table a second time at all.

I suspect the following will give you what you want. Since it's not joining the table a second time, the only rows it will see are the ones with Done=1 so the counts will be correct:

SELECT a.ID, COUNT (a.Done) as Count_Done,a.Cycle,a.Week 
FROM #Table a
WHERE a.Done=1
GROUP BY a.ID,a.Cycle,a.Week

If you want to include id/cycle/week combinations that have the count as 0, I suggest using SUM instead of COUNT (if your done values are truly 0/1):

SELECT a.ID, SUM(a.Done) as Count_Done,a.Cycle,a.Week 
FROM #Table a
GROUP BY a.ID,a.Cycle,a.Week

Or maybe

SELECT a.ID, COUNT(CASE WHEN a.Done=1 THEN 1 END) as Count_Done,a.Cycle,a.Week 
FROM #Table a
GROUP BY a.ID,a.Cycle,a.Week

How can I get max 20 group by count between min and max value

You can use the NTILE() function to divide the data into specific number of groups.

SELECT
bucket_no,
min(price),
max(price),
count(*)
FROM
(SELECT
price,
ntile(20)Over(order by price asc) as bucket_no
from table)A
group by bucket_no;

If you want to do this for every category of product, then you can use partition by in the NTILE() function.

Refer this for further understanding:
https://www.mysqltutorial.org/mysql-window-functions/mysql-ntile-function/

SELECT COUNT with Group BY only return value of 2

I have checked your table, every school do have 2 rows.
maybe u want to count how many distinct school there are, so change the sql to:

select count(distinct School )from engoralgrade3

or u want to distinct the school name, try :

select distinct School from engoralgrade3

---

Related Topics