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
Why Do Multiple-Table Joins Produce Duplicate Rows
What Are Indexes and How to Use Them to Optimize Queries in My Database
Sql: Last_Value() Returns Wrong Result (But First_Value() Works Fine)
How to Write SQL Using Speech Recognition
How to Select the First Row Per Group in an SQL Query
Concatenate Many Rows into a Single Text String with Grouping
Are Determinants and Candidate Keys Same or Different Things
Nolock VS. Transaction Isolation Level
Easiest Way to Eliminate Nulls in Select Distinct
SQL Injections with Prepared Statements
Fastest Way to Export Blobs from Table into Individual Files
How to Find All Rows with a Null Value in Any Column Using Postgresql
How to Implement a Substring Search in SQL
Oracle SQL: How to Use More Than 1000 Items Inside an in Clause
How to Tell If I Have Uncommitted Work in an Oracle Transaction