How to Count All Rows with The Same Id with Count

Is it possible to count all rows with the same id with COUNT?

If all you need is a count of the number of rows where player_id is 1, then you can do this:

SELECT count(*)
FROM your_table_name
WHERE player_id = 1;

If you want to count the number of rows for each player_id, then you will need to use a GROUP BY:

SELECT player_id, count(*)
FROM your_table_name
GROUP BY player_id;

SQL get count of all rows that have the same id

Use GROUP BY

SELECT story_id, Count(story_id) as num_comments FROM story_comment  
GROUP BY story_id

The GROUP BY statement is used in conjunction with the aggregate
functions to group the result-set by one or more columns.

SQL Query count row of same id in a different table

Try following query. This will give you all the Rows from equipment_entity Table and the count of rows in resource table for each equipment.

SELECT e.*, count(*) AS count FROM `equipment_entity` AS e left join `resources` AS r ON e.id = r.equipment_class_id GROUP BY e.id

count rows with same id and print

Update Your Query With This

$sql = $mysqli->query("SELECT count(*),u_id as 'count_users' FROM users_photos group by u_id");

Find the count of IDs that have the same value

You can use a CTE to generate a list of Drops values that have more than one corresponding ID value, and then JOIN that to Posts to find all rows which have a Drops value that has more than one Post:

WITH CTE AS (
  SELECT Drops
  FROM Posts
  GROUP BY Drops
  HAVING COUNT(*) > 1
)
SELECT P.*
FROM Posts P
JOIN CTE ON P.Drops = CTE.Drops

Output:

ID  Drops
1   A
3   A
7   B
18  B

If desired you can then count those posts in total (or grouped by Drops value):

WITH CTE AS (
  SELECT Drops
  FROM Posts
  GROUP BY Drops
  HAVING COUNT(*) > 1
)
SELECT COUNT(*) AS newcnt
FROM Posts P
JOIN CTE ON P.Drops = CTE.Drops

Output

newcnt
4

Demo on SQLFiddle

Count rows which has the same ID and display on the table

First of all, saving the calculated value back into table is not only unnecessary but bad design.

Options:

1. build a report that counts records with an expression in textbox

2. build aggregate query then another query joining aggregate query to the table

3. DCount() domain aggregate function in query

Retrieving records with the same ID where the number of occurrences of one value in a column is greater than occurrences of another value

Edit: Updated answered = 0 outside of subquery.

ROW_NUMBER() is going to be your friend on this one. You can use it determine all the scenarios where the last entry for a particular callid = 0. I'm assuming there's some timestamp for each event that occurs.

SELECT
    *
FROM (
    SELECT
        callid
        ,... --Other columns needed
        ,ROW_NUMBER() OVER (PARTITION BY callid ORDER BY <timestamp column> DESC) AS rn --Order descending so the latest entry = 1
    FROM t1
) as calls
where calls.rn = 1
and answered = 0    

---

Related Topics