Menu
The opposite of DISTINCT is ALL. Because ALL is the default, it is typically not included.
SELECT * FROM <TableName>; This SQL query will select all columns and all rows from the table. For example: SELECT * FROM [Person].
To exclude columns, you use the REPLACE() and GROUP_CONCAT() functions to generate the column names you wish to include in your SELECT statement later. You can use the result to create your SELECT statement: SELECT address,age,email,id,name,phone FROM Students; And that's how you use the information_schema.
This is significantly faster than the EXISTS way:
SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN
(SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1)
The thing that is incorrect with your query is that you are grouping by email and name, that forms a group of each unique set of email and name combined together and hence
aaron and [email protected]
christy and [email protected]
john and [email protected]
are treated as 3 different groups rather all belonging to 1 single group.
Please use the query as given below :
select emailaddress,customername from customers where emailaddress in
(select emailaddress from customers group by emailaddress having count(*) > 1)
select CustomerName,count(1) from Customers group by CustomerName having count(1) > 1
How about
SELECT EmailAddress, CustomerName FROM Customers a
WHERE Exists ( SELECT emailAddress FROM customers c WHERE a.customerName != c.customerName AND a.EmailAddress = c.EmailAddress)
Just for fun, here's another way:
;with counts as (
select CustomerName, EmailAddress,
count(*) over (partition by EmailAddress) as num
from Customers
)
select CustomerName, EmailAddress
from counts
where num > 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With