How to simplify following SQL query to invoke count method only once?

how to execute a sql query only if another sql query has no results
how do i use the output of one query in another in sql
sql if query returns no results then
use the results of a query as a field in another query
sql use results of one query in another
sql query calling another query
how to make select query faster in sql server
return a value if no rows are found sql

I have following SQL code

select surname,
(select count(surname))  
from employees
group by surname
having count(surname) > 1
order by surname asc; 

I need to invoke count method only once.

There are a million ways to do that, but as Gordon pointed out: you shouldn't worry with count being executed multiple times - let the database take care of it. But if you somehow want to avoid writing it twice, try these options:

Subquery

SELECT *
FROM 
  (SELECT surname, count(surname) AS c 
   FROM employees GROUP BY surname) j
WHERE c > 1
ORDER BY surname

Or a CTE

WITH j AS (
SELECT surname, count(surname) AS c 
   FROM employees GROUP BY surname)  
SELECT * FROM j WHERE c > 1
ORDER BY surname

How to Execute a SQL Query Only if Another SQL Query has no , If that predicate returns no rows, they wanted to run another query using a different predicate. The following query is formally correct and runs without any enough to avoid the second query, because the first one does yield 9 rows. 3 If the count is null/zero return the second value, otherwise the first The SQL COUNT(), AVG() and SUM() Functions. The COUNT() function returns the number of rows that matches a specified criterion. The AVG() function returns the average value of a numeric column. The SUM() function returns the total sum of a numeric column. COUNT() Syntax

This uses count() only once but uses a subquery

SELECT * 
FROM (
    SELECT 
         surname
        ,count(surname) as surname_count
    FROM employees
    GROUP BY
        surname
)
WHERE
    surname_count > 1
ORDER BY
    surname ASC

More efficient SQL with query planning and optimization (article , SQL is a declarative language - each query declares what we want the SQL If the table is only 10 rows long, then a full table scan only requires looking at 10 (improvements that always result in better performance, like simplifying 5*10 into 50). However, once you start dealing with bigger data sets, you'll start to care� The DISTINCT can come only once in a given select statement. Syntax : COUNT(DISTINCT expr,[expr]) Example : To get unique number of rows from the 'orders' table with following conditions - 1. only unique cust_code will be counted, 2. result will appear with the heading "Number of employees", the following SQL statement can be used :

How to improve SQL Server query performance in .NET, 14 tips on working with SQL Server, Entity Framework, LINQ, and other If your ORM is capable of handling multi-dataset returns, use this to reduce your efficient to place them in a stored procedure and leave the heavy lifting to the database. should be of type IList to force the query to be executed once and only once. Except for the COUNT() function, SQL aggregate functions ignore null. You can use aggregate functions as expressions only in the following: The select list of a SELECT statement, either a subquery or an outer query. A HAVING clause AVG. The AVG() function returns the average values in a set. The following illustrates the syntax of the AVG

4. Query Performance Optimization, Once you're sure your queries retrieve only the data you need, you can look for MySQL can use several access methods to find and return a row. An index can' t reduce the number of rows examined for a query like this one. The next step in the query lifecycle turns a SQL query into an execution plan for� The Invoke-Sqlcmd cmdlet runs a script containing the languages and commands supported by the SQL Server SQLCMD utility. The commands supported are Transact-SQL statements and the subset of the XQuery syntax that is supported by the database engine. This cmdlet also accepts many of the commands supported natively by SQLCMD, such as GO and QUIT. This cmdlet also accepts the SQLCMD scripting

SQL Tutorial: How To Write Better Querie, However, most forget that SQL isn't just about writing queries, which is It follows the “learn once, use anywhere” principle, so it's a great investment of your time! what happens internally when you press the shortcut to run the query. Take a look at the following section to learn more about anti-patterns� And here is the simple T-SQL script I use in the above example. It is now so nice to be able to see version, max memory, PLE, logical CPU count, tempdb files, CPU signal wait, listener name, IP Address, failover mode, synchronization health etc. for all of the availability groups from one table which gets updated frequently values.

Query Processing Architecture Guide, Execution modes; SQL Statement Processing; Stored Procedure and Trigger Execution When more than one logical operator is used in a statement, NOT is The following query finds only products under models 20 and 21 that are red. plan for the state of the database every time the statement is run. I am wondering how to write this query. I know this actual syntax is bogus, but it will help you understand what I am wanting. I need it in this format, because it is part of a much bigger query. SELECT distributor_id, COUNT(*) AS TOTAL, COUNT(*) WHERE level = 'exec', COUNT(*) WHERE level = 'personal' I need this all returned in one query.

Comments
  • Don't worry about using count() more than once. Let the database worry about optimization.
  • As Gordon Linoff notes, you don't need to bother with count(). And you don't need the sub-select. Just select surname, count(surname) from employees group by surname having count(surname) > 1 order by surname;
  • On sqlfiddle it doesn't work .
  • Thank you guys, Jim Jones answer's works perfectly. But my code with two duplicates show my poor knowledge about sql, i'm right? ;>