Select Where Count of One Field Is Greater Than One

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...

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

Select where Count of 1 field is greater than a value and count of another is less than a value

If you want the details about the FCs that have more than one unique address then this query will give you that:

select c.* from customers c
join (
select FC
from customers
where statustype = 'dc'
group by fc having count(distinct Address1) > 1
) a on c.FC = a.FC

SQL group by count where count greater than

You should put this condition in the HAVING-clause:

select Code, Qty, Count(Qty) Qty
from Product
where ItemName = 'Banana'
Group by Code
having count(Qty) > 1
order by 3 desc

HAVING is evaluated after GROUP BY while WHERE is evaluated before, meaning that WHERE-clauses will filter on recordlevel while HAVING-clauses filter on aggregates.

For a more detailed explanation, check SQL - having VS where

I would also recommend you to read Logical Processing Order of the SELECT statement

SQL query to fetch a column where count is greater than 1 in the same table

This should do it:

 SELECT SHIPMENT_GI, count(*)
FROM shipment_cost
WHERE COST_TYPE='B'
GROUP BY SHIPMENT_GI
HAVING COUNT(*) > 1

SQL - Select all rows and count only column greater than 1

You want to aggregate your rows and get one result row per name. This translates to GROUP BY name in SQL. For counting use COUNT and inside use CASE WHEN to decide what to count.

select name, count(case when value > 0 then 1 end)
from mytable
group by name
order by name;

This works because COUNT only counts non-null occurences. We could just as well use SUM for counting: sum(case when value > 0 then 1 else 0 end).

In your example there is only 0 and 1. If these are the only possible values, you can just add them up:

select name, sum(value)
from mytable
group by name
order by name;

Select Where Count() of multiple columns is greater than one

You can group by the columns you want to deduplicate, and select the minimum date from each group:

SELECT ClaimID, ClaimLine, MIN(Date)
FROM Table
GROUP BY ClaimID, ClaimLine
ORDER BY ClaimID, ClaimLine

If you further only want to see the ClaimIDs and ClaimLines that have been duplicated, you can add a HAVING clause:

SELECT ClaimID, ClaimLine, MIN(Date)
FROM Table
GROUP BY ClaimID, ClaimLine
HAVING COUNT(*) > 1
ORDER BY ClaimID, ClaimLine

Count Case Statement - When One Field Greater Than Another

For the count value You could use sum instead of count adding 1 when the condition is true and 0 when false

In sql for aggregated select the select for columns not in aggregated function and not mentioned in group by is deprecated, in the most recent version of mmysql is not allowed and for the older version the result for these values in unpredicatble so you should in group by then column that you have not in aggregation function in select eg:

    select b.brandName
, b.BrandCode
, p.ProductVendorStockNumber
,sum(Case When p.IMAP > p.MSRP THEN 1 ELSE 0 END) as my_count
from products p
join brands b on p.brandID = b.brandID
where b.assignedTo = 'Steve' and p.IMAP > p.MSRP and status = 1
GROUP BY b.BrandName, b.BrandCode, p.ProductVendorStockNumber

or filter the result using the rows without aggregation and a join on the right aggregated rows

Find values with group count greater than 1

Try this -

select id from table group by id having count(distinct color) > 1

You can search for sql having clause for more info.

Here is one such result:

https://www.w3schools.com/sql/sql_having.asp

Postgres: select all row with count of a field greater than 1

SELECT * 
FROM product_price_info
WHERE name IN (SELECT name
FROM product_price_info
GROUP BY name HAVING COUNT(*) > 1)


Related Topics



Leave a reply



Submit