Select Count(*) as Count - How to Use This Count

Use COUNT(*) AS `COUNT` in MYSQL Where Clause

Use HAVING over WHERE with GROUP BY

SELECT `Id`, COUNT(*) AS `COUNT`
FROM `testproductdata`
GROUP BY `Id`
HAVING  `COUNT` > 1
ORDER BY `COUNT` DESC;

And I suggest to use relevant name for expression on count(*) as total_count than COUNT.

Change query as below:

SELECT `Id`, COUNT(*) AS `total_count`
FROM `testproductdata`
GROUP BY `Id`
HAVING  `total_count` > 1
ORDER BY `total_count` DESC;

How to re-name and reference COUNT(*) in a SELECT statement?

You can alias a column by simply putting a name after it, optionally with the keyword AS in between. It's essentially the same as you already do with the tables.

SELECT school_name,
       (SELECT count(*)
               FROM liason_to l
               WHERE l.school_name = s.school_name) AS numliasons
       FROM school s;

or simply

SELECT school_name,
       (SELECT count(*)
               FROM liason_to l
               WHERE l.school_name = s.school_name) numliasons
       FROM school s;

But you cannot use aliases in the WHERE clause (aliasing is happening after the records have been selected by the criteria in the WHERE clause). You have to repeat the expession.

SELECT school_name,
       (SELECT count(*)
               FROM liason_to l
               WHERE l.school_name = s.school_name) numliasons
       FROM school s
       WHERE (SELECT count(*)
                     FROM liason_to l
                     WHERE l.school_name = s.school_name) > 0;

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;

select count(*) vs keep a counter

Keeping a separate count column in addition to the real data is a denormalisation. There are reasons why you might need to do it for performance, but you shouldn't go there until you really need to. It makes your code more complicated, with more chance of inconsistencies creeping in.

For the simple case where the query really is just SELECT COUNT(property) FROM table WHERE property=..., there's no reason to denormalise; you can make that fast by adding an index on the property column.

Select * vs Select count(*), which one is faster to count row?

select count(*) is faster than select *:

select * scan all the rows:

If your table have index, mysql query optimizer use index for select count(*):

And select * will take all columns out.
like this example, it took 67.7ms to take all the datas;

however, select count(*) only took 9.2ms to take the count.

Is it possible to specify condition in Count()?

If you can't just limit the query itself with a where clause, you can use the fact that the count aggregate only counts the non-null values:

select count(case Position when 'Manager' then 1 else null end)
from ...

You can also use the sum aggregate in a similar way:

select sum(case Position when 'Manager' then 1 else 0 end)
from ...

How to select all columns and count from a table?

A combination of a window function with DISTINCT ON might do what you are looking for:

SELECT DISTINCT ON (hash_value)
       *, COUNT(*) OVER (PARTITION BY hash_value) AS total_rows
FROM   top_teams_team
-- ORDER  BY hash_value, ???
;

DISTINCT ON is applied after the window function, so Postgres first counts rows per distinct hash_value before picking the first row per group (incl. that count).

The query picks an arbitrary row from each group. If you want a specific one, add ORDER BY expressions accordingly.

This is not "a count of values for the hash_value column" but a count of rows per distinct hash_value. I guess that's what you meant.

Detailed explanation:

• Best way to get result count before LIMIT was applied
• Select first row in each GROUP BY group?

Depending on undisclosed information there may be (much) faster query styles ...

• Optimize GROUP BY query to retrieve latest row per user

---

Related Topics