Understanding the difference between COUNT() and COUNT(DISTINCT) in SQL is crucial for accurate data analysis.
COUNT() returns the total number of rows that match your query criteria, including duplicates, while COUNT(DISTINCT) returns the number of unique values in a specified column, effectively eliminating duplicates from the count.
For example, if you have a table of customer orders where a single customer can place multiple orders, COUNT(customer_id) would give you the total number of orders, whereas COUNT(DISTINCT customer_id) would tell you how many unique customers have placed orders.
The choice between these functions depends on your specific reporting needs. Use COUNT() when you need the total number of records, such as counting all sales transactions or total number of website visits.
Use COUNT(DISTINCT) when you need to know unique occurrences, like the number of different products sold or unique visitors to your website. It's also worth noting that COUNT(*) counts all rows including NULL values, while COUNT(column_name) excludes NULL values from that specific column, which can lead to different results depending on your data structure.
Example
-- Example table: customer_orders -- customer_id | order_date | product_id -- 1 | 2024-01-01 | 100 -- 1 | 2024-01-02 | 101 -- 2 | 2024-01-01 | 100 -- 3 | 2024-01-03 | 102 -- Count all orders SELECT COUNT(*) as total_orders FROM customer_orders; -- Result: 4 (counts all rows) -- Count unique customers who placed orders SELECT COUNT(DISTINCT customer_id) as unique_customers FROM customer_orders; -- Result: 3 (counts unique customer_ids: 1, 2, 3) -- Count unique products ordered SELECT COUNT(DISTINCT product_id) as unique_products FROM customer_orders; -- Result: 3 (counts unique product_ids: 100, 101, 102) -- Compare regular COUNT with COUNT DISTINCT SELECT COUNT(customer_id) as total_orders, COUNT(DISTINCT customer_id) as unique_customers FROM customer_orders; -- Result: total_orders = 4, unique_customers = 3
String interpolation, introduced in C# 6.0, provides a more readable and concise way to format strings compared to traditional concatenation (+) or string.Format(). Instead of manually inserting variables or placeholders, you can use the $ symbol before a string to directly embed expressions inside brackets.
string name = "Walt"; string job = 'Software Engineer'; string message = $"Hello, my name is {name} and I am a {job}"; Console.WriteLine(message);
This would produce the final output of:
Hello, my name is Walt and I am a Software Engineer
String interpolation can also be chained together into a multiline string (@) for even cleaner more concise results:
string name = "Walt"; string html = $@" <div> <h1>Welcome, {name}!</h1> </div>";
Reading a file line by line is useful when handling large files without loading everything into memory at once.
✅ Best Practice: Use File.ReadLines() which is more memory efficient.
foreach (string line in File.ReadLines("file.txt")) { Console.WriteLine(line); }
Why use ReadLines()?
Reads one line at a time, reducing overall memory usage. Ideal for large files (e.g., logs, CSVs).
Alternative: Use StreamReader (More Control)
For scenarios where you need custom processing while reading the contents of the file:
using (StreamReader reader = new StreamReader("file.txt")) { string? line; while ((line = reader.ReadLine()) != null) { Console.WriteLine(line); } }
Why use StreamReader?
Lets you handle exceptions, encoding, and buffering. Supports custom processing (e.g., search for a keyword while reading).
When to Use ReadAllLines()? If you need all lines at once, use:
string[] lines = File.ReadAllLines("file.txt");
Caution: Loads the entire file into memory—avoid for large files!
When working with SQL Server, you may often need to count the number of unique values in a specific column. This is useful for analyzing data, detecting duplicates, and understanding dataset distributions.
To count the number of unique values in a column, SQL Server provides the COUNT(DISTINCT column_name) function. Here’s a simple example:
COUNT(DISTINCT column_name)
SELECT COUNT(DISTINCT column_name) AS distinct_count FROM table_name;
This query will return the number of unique values in column_name.
column_name
If you need to count distinct combinations of multiple columns, you can use a subquery:
SELECT COUNT(*) AS distinct_count FROM (SELECT DISTINCT column1, column2 FROM table_name) AS subquery;
This approach ensures that only unique pairs of column1 and column2 are counted.
column1
column2
By leveraging COUNT(DISTINCT column_name), you can efficiently analyze your database and extract meaningful insights. Happy querying!
Register for my free weekly newsletter.