Can I do a max(count(*)) in SQL?
Use:
SELECT m.yr,
COUNT(*) AS num_movies
FROM MOVIE m
JOIN CASTING c ON c.movieid = m.id
JOIN ACTOR a ON a.id = c.actorid
AND a.name = 'John Travolta'
GROUP BY m.yr
ORDER BY num_movies DESC, m.yr DESC
Ordering by num_movies DESC
will put the highest values at the top of the resultset. If numerous years have the same count, the m.yr
will place the most recent year at the top... until the next num_movies
value changes.
Can I use a MAX(COUNT(*)) ?
No, you can not layer aggregate functions on top of one another in the same SELECT clause. The inner aggregate would have to be performed in a subquery. IE:
SELECT MAX(y.num)
FROM (SELECT COUNT(*) AS num
FROM TABLE x) y
SQL select MAX(COUNT)
I'd try with a ORDER BY max DESC LIMIT 1, where maximum is the count(*) field. Something like:
SELECT "name", count(*) maximum FROM "users"
INNER JOIN "microposts" ON "microposts"."user_id" = "users"."id"
GROUP BY users.id
ORDER BY maximum DESC
LIMIT 1
I dont' have mysql available now, so I'm doing this on the paper (and it might not work), but it's just an orientation.
Using sub-queries in SQL to find max(count())
You don't need a correlated subquery for what you are doing. Here is one way based on your query:
select CustomerNum, count(CustomerNum)
from Rentals R
group by CustomerNum
having count(CustomerNum) = (select max(cnt)
from (select CustomerNum, count(CustomerNum) as cnt
from Rentals
group by CustomerNum
) rc
);
I would be inclined to move the subquery to the from
clause and use subqueries:
select rc.*
from (select CustomerNum, count(CustomerNum) as cnt
from Rentals R
group by CustomerNum
) rc join
(select max(cnt) as maxcnt
from (select CustomerNum, count(CustomerNum) as cnt
from Rentals
group by CustomerNum
) rc
) m
on rc.cnt = m.maxcnt;
These are standard SQL and should work in both systems. In practice, I'd probably find a way to use top
or row_number()
on SQL Server 2008.
Maximum Count of Distinct Values in SQL
You can do this with nested aggregation:
select max(cnt)
from (select person_id, count(*) as cnt
from mytable
group by person_id
) p;
If you actually want the person, you can also do:
select person_id, count(*) as cnt
from mytable
group by person_id
order by count(*) desc
limit 1;
SELECT id HAVING maximum count of id
SELECT color_id AS id, COUNT(color_id) AS count
FROM products
WHERE item_id = 1234 AND color_id IS NOT NULL
GROUP BY color_id
ORDER BY count DESC
LIMIT 1;
This will give you the color_id and the count on that color_id ordered by the count from greatest to least. I think this is what you want.
for your edit...
SELECT color_id, COUNT(*) FROM products WHERE color_id = 3;
how to multiply a numerical field in count in sql
Use sum()
instead of count()
:
Select sum(node.weight) as count, ans.cn_gender, poll.pollname
From report.cm_marital_satisfaction ans Join
poll_management.POLLES poll
on ans.cn_pollId = poll.id Join
poll_management.POSITIONS node
on ans.cn_positionId = node.id
Where ans.cn_pollId in (113)
group by ans.cn_gender, poll.pollname
SUM of grouped COUNT in SQL Query
SELECT name, COUNT(name) AS count
FROM table
GROUP BY name
UNION ALL
SELECT 'SUM' name, COUNT(name)
FROM table
OUTPUT:
name count
-------------------------------------------------- -----------
alpha 1
beta 3
Charlie 2
SUM 6
Fastest way to count exact number of rows in a very large table?
Simple answer:
- Database vendor independent solution = use the standard =
COUNT(*)
- There are approximate SQL Server solutions but don't use COUNT(*) = out of scope
Notes:
COUNT(1) = COUNT(*) = COUNT(PrimaryKey) just in case
Edit:
SQL Server example (1.4 billion rows, 12 columns)
SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK)
-- NOLOCK here is for me only to let me test for this answer: no more, no less
1 runs, 5:46 minutes, count = 1,401,659,700
--Note, sp_spaceused uses this DMV
SELECT
Total_Rows= SUM(st.row_count)
FROM
sys.dm_db_partition_stats st
WHERE
object_name(object_id) = 'MyBigtable' AND (index_id < 2)
2 runs, both under 1 second, count = 1,401,659,670
The second one has less rows = wrong. Would be the same or more depending on writes (deletes are done out of hours here)
How to count number of records per day?
select DateAdded, count(CustID)
from Responses
WHERE DateAdded >=dateadd(day,datediff(day,0,GetDate())- 7,0)
GROUP BY DateAdded
Related Topics
Why Does This SQL Code Give Error 1066 (Not Unique Table/Alias: 'User')
Get the Last Day of the Month in SQL
List Columns with Indexes in Postgresql
How to Insert Multiple Records and Get the Identity Value
Call a Set-Returning Function with an Array Argument Multiple Times
Which SQL Query Is Better, Match Against or Like
How to Group on Continuous Ranges
How Does a Recursive Cte Run, Line by Line
MySQL Returning the Top 5 of Each Category
Regular Expressions in Db2 SQL
Does Sparksql Support Subquery
Sub Queries in Check Constraint
How to Check If a Stored Procedure Exists Before Creating It
Solution For: Store Update, Insert, or Delete Statement Affected an Unexpected Number of Rows (0)
Concatenate Results from a SQL Query in Oracle