... Where Count(Col) > 1

... where count(col) 1

Use the having clause for comparing aggregates.

Also, you need to group by what you're aggregating against for the query to work correctly. The following is a start, but since you're missing a group by clause still it won't quite work. What exactly are you trying to count?

select fk, count(value) 
from table 
group by fk
having count(value) > 1;

SQL query for finding records where count 1

Use the HAVING clause and GROUP By the fields that make the row unique

The below will find

all users that have more than one payment per day with the same account number

SELECT 
 user_id,
 COUNT(*) count
FROM 
 PAYMENT
GROUP BY
 account,
 user_id,
 date
HAVING COUNT(*) > 1

Update
If you want to only include those that have a distinct ZIP you can get a distinct set first and then perform you HAVING/GROUP BY

 SELECT 
    user_id,
    account_no, 
    date,
    COUNT(*)
 FROM
    (SELECT DISTINCT
            user_id,
            account_no, 
            zip, 
            date
         FROM
            payment 
    ) payment
 GROUP BY
    user_id,
    account_no,
    date
 HAVING COUNT(*) > 1

Group by column Having COUNT (column) 1 in a single column

this isn't an answer, just a question/comment:

Without knowing any more details about your database schema, I'm wondering if you're simply using the wrong table for your group-by clause?

If your "inv_mast" table only contained unique "item_id"s, then you'd never get a count higher than one from that particular table.

Try counting on the IDs on the "inv_bin"-table instead:

SELECT item_id 
FROM inv_bin
GROUP BY item_id HAVING COUNT(item_id) > 1

Select where count of one field is greater than one

Use the HAVING, not WHERE clause, for aggregate result comparison.

Taking the query at face value:

SELECT * 
  FROM db.table 
HAVING COUNT(someField) > 1

Ideally, there should be a GROUP BY defined for proper valuation in the HAVING clause, but MySQL does allow hidden columns from the GROUP BY...

Is this in preparation for a unique constraint on someField? Looks like it should be...

How to get count for each combination of col1 & col2 on specific conditions for col3

Include those conditions in count.

SELECT col1, col2,
count(case when col3 is null OR col3 = '' OR col3 = 'N/A' OR col3 = 'Unknown' OR col3 = 'NULL' then 1 end) as MainCount
FROM table_name2
GROUP BY col1, col2
ORDER BY col1, col2;

What does select count(1) from table_name on any database tables mean?

The parameter to the COUNT function is an expression that is to be evaluated for each row. The COUNT function returns the number of rows for which the expression evaluates to a non-null value. ( * is a special expression that is not evaluated, it simply returns the number of rows.)

There are two additional modifiers for the expression: ALL and DISTINCT. These determine whether duplicates are discarded. Since ALL is the default, your example is the same as count(ALL 1), which means that duplicates are retained.

Since the expression "1" evaluates to non-null for every row, and since you are not removing duplicates, COUNT(1) should always return the same number as COUNT(*).

Oracle - HAVING COUNT 1 not giving results with multiple columns

If you add the PK to the group by you can't get more then one row per group because the PK column is unique.

If you want additional columns but group by a subset, you can use a window function for that:

select *
from (
  select pk_crl_id,
         crl_id,
         count(*) over (partition by crl_id) as clr_id_count
  from crl_table
  where crl_id <> 0
) t
where clr_id_count > 1

SQL: count rows where column = a value AND another column is the same as values in the group where the first condition is true?

You can use:

SELECT employee_id,
       MAX( CASE WHEN status = 9 AND cnt > 1 THEN cnt ELSE 0 END ) AS count_9,
       SUM( cnt ) AS count_all
FROM   (
  SELECT employee_id,
         status,
         dt,
         COUNT(*) AS cnt
  FROM   table_name
  GROUP BY employee_id, status, dt
)
GROUP BY employee_id
ORDER BY employee_id;

Which, for your sample data:

CREATE TABLE table_name ( employee_id, status, dt ) AS
SELECT 1, 9, DATE '2020-10-19' FROM DUAL UNION ALL
SELECT 1, 7, DATE '2001-07-16' FROM DUAL UNION ALL
SELECT 1, 9, DATE '2020-10-19' FROM DUAL UNION ALL
SELECT 2, 5, DATE '2011-08-11' FROM DUAL UNION ALL
SELECT 2, 9, DATE '2012-12-25' FROM DUAL UNION ALL
SELECT 2, 9, DATE '2013-11-19' FROM DUAL UNION ALL
SELECT 3, 5, DATE '2016-06-05' FROM DUAL UNION ALL
SELECT 3, 4, DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 4, 9, DATE '2018-02-15' FROM DUAL UNION ALL
SELECT 4, 9, DATE '2018-02-15' FROM DUAL UNION ALL
SELECT 4, 9, DATE '2018-02-15' FROM DUAL;

Outputs:

EMPLOYEE_ID | COUNT_9 | COUNT_ALL
----------: | ------: | --------:
          1 |       2 |         3
          2 |       0 |         3
          3 |       0 |         2
          4 |       3 |         3

db<>fiddle here

MySQL query - joining 3 tables count and group by one column

Your subquery makes no sense to me. The entire query is as simple as:

SELECT events.id, order_items.category, SUM(order_items.quantity) AS count FROM events left join orders on events.id=orders.event_id LEFT JOIN order_items ON order_items.order_id = orders.id GROUP BY events.id, order_items.category

I think the key here is that you want to group by two columns. Grouping by event.id should be the same as event.title as long as title is unique. But you also need to group by category to get the proper total. And since you want the count, you actually need to include that in the columns you select.

But, order.id is not the same as event.id, event.title is not the same as order_items.category, and a.title is not the count.

---

Related Topics