SQL COUNT() Function

The SQL COUNT() is a function, and return total number of records or rows from query result.

The SQL COUNT() function will not count the NULL value records or rows.

The SQL COUNT() function is supports all types of column.

It can be used in SQL SELECT statement as well in SQL WHERE clause.

Example 1: To determine how many rows there are for each distinct entry in a table, use the GROUP BY function. The number of rows in the table will be totaled if COUNT is used without the GROUP BY clause.

With GROUP BY added, we can COUNT the overall occurrences of each distinct value in the column.

Now, for the demonstration follow the below steps:

Step 1: Create a database

we can use the following command to create a database called geeks.

CREATE DATABASE Student;

Step 2: Use database

Use the below SQL statement to switch the database context to geeks:

USE Student;

Step 3: Table definition

We have the following demo_table in our Student's database.

CREATE TABLE demo_table(
NAME VARCHAR(20),
AGE int,
CITY VARCHAR(10));

Step 4: Insert data into a table

INSERT INTO demo_table VALUES ('Romy',23,'Delhi'),
('Pushkar',23,'Delhi'),
('Nikhil',24,'Punjab'),
('Rinkle',23,'Punjab'),
('Samiksha',23,'Banglore'),
('Ashtha',24,'Banglore'),
('Satish',30,'Patna'),
('Girish',30,'Patna');

Step 5: View the content

Execute the below query to see the content of the table

SELECT * FROM demo_table;

Step 6: use of COUNT without ORDER BY statement

COUNT(column_name) counts non-NULLs only in the specified column name.

Syntax(count all rows):

SELECT COUNT(*)
FROM table_name;

Query:

SELECT COUNT(*) FROM demo_table;

The result is 8, as we have 8 entries in our demo_table.

Step 7: use GROUP BY

For counting the unique values in the AGE column.

Query:

SELECT AGE, COUNT(*) as COUNT from demo_table GROUP BY AGE;

For counting the unique values in the CITY column.

SELECT CITY,COUNT(*) as COUNT from demo_table GROUP BY CITY;

Example 2: You’d like to determine how many rows a table has.

Our database has a table named pet with data in the following columns: id, eID (electronic identifier), and name.

id	eID	name
1	23456	sparky
2	23457	mily
3	NULL	lessy
4	NULL	carl
5	34545	maggy

Let’s count all rows in the table.

COUNT(*) counts the total number of rows in the table:

SELECT COUNT(*) as count_pet
FROM pet;

Output:

Instead of passing in the asterisk as the argument, you can use the name of a specific column:

SELECT COUNT(id) as count_pet
FROM pet;

In this case, COUNT(id) counts the number of rows in which id is not NULL.

Discussion:

• To count the rows in a table, use the COUNT aggregate function. This function returns the number of rows in the table for the specified column after receiving the name of the column as an argument (for example, "id") (e.g., 5).
• The function will only count non-NULL values if you supply a column rather than an asterisk, as was previously mentioned. Our table's primary key, id, has distinct and non-NULL values, making it an excellent option to count the total number of rows in the table.
• Naturally, you may substitute the asterisk character as the parameter to COUNT if you want to count every row. This will include rows with NULL values in any column to be counted.

Example 3: Here’s an example of counting the number of rows for a column that has NULL values:

SELECT COUNT(eID) as count_pet
FROM pet;

Output:

To count the number of rows in a table, it is advised that you give the * character or a primary key column to the COUNT function. As we've seen, both strategies will provide the same outcome.

Example 4: The COUNT function is the most obvious approach to obtain the number of rows in the table. Two methods are frequently used for this: COUNT(*) and COUNT (1).

Let’s look at COUNT(*) first.

SELECT COUNT(*)
FROM dbo.bigTransactionHistory;

This query's STATISTICS IO output demonstrates how busy SQL Server is! To answer this inquiry, over 100,000 logical reads, physical reads, and even read-ahead reads must be performed.

Example 5: Now, let’s look at the behavior of COUNT(1).

SELECT COUNT(1)
FROM dbo.bigTransactionHistory;

We can see from STATISTICS IO that we have a large number of logical reads – over 100,000.

The number of distinct values in the column or expression is returned by the COUNT DISTINCT function. Unless every value in the specified column is NULL, the COUNT DISTINCT function ignores NULL values when they are encountered.

The COUNT DISTINCT function returns zero if there were no matching rows (0).

Provides a count of all non-NULL values in the collection.

COUNTDISTINCT can only be applied to single-assigned characteristics; multi-assigned attributes are not supported. The use of a multi-assign attribute produces false results.

Syntax:

SELECT COUNT(DISTINCT column) FROM table;

The attribute column in the table's unique entries would be counted by this statement. Repeated entries are only counted once when using DISTINCT to ensure this.

Example 1: Consider the following table, employees, created as follows:

Step 1: Create Table

CREATE TABLE employees(
  emp_id int,
  emp_name varchar(20),
  dept varchar(20),
  age int
);

Step 2: Insert Record

INSERT INTO employees(emp_id,emp_name,dept,age)
VALUES(1, "John", "Intern",25);

INSERT INTO employees(emp_id,emp_name,dept,age)
VALUES(2, "David", "Intern",30);

INSERT INTO employees(emp_id,emp_name,dept,age)
VALUES(3, "Mike", "Engineer",29);

INSERT INTO employees(emp_id,emp_name,dept,age)
VALUES(4, "Alex", "HR",27);

Step 3: Display all Records:

select * from employees;

Output:

id	name	dept	     age
1	John	Intern	     25
2	David	Intern	     30
3	Mike	Engineer     29
4	Alex	HR	     27

Step 4:

SELECT COUNT(DISTINCT dept) from employees

This SQL statement counts every unique department name in the employee table.

There are three distinct departments, hence the SQL statement returns 3. The DISTINCT clause only includes one intern because there are two employees who are interns. The remaining two departments, Engineer and HR, are each counted once because they are distinct, making a total of 3.

Example 2: Count Distinct Example:

Consider the following employee table.

emp_id	emp_name	emp_phone	emp_gender	department
101	Kalyan Roy	9620139678	    M	            HR
102	Rajesh Sharma	9611895588	    M	           ADMIN
103	Rupali Sharma	8884692570	    F	           SALES
104	Dipankar Sen	9957889640	    M	            HR
105	Sunitha Rai	9742067708	    F	           SALES

Step 1: Create Table

CREATE TABLE Teradatapoint.employee 
(
emp_id integer,
emp_name varchar(50),
emp_phone varchar(10),
emp_gender char(1),
department varchar(20)
)primary index(emp_id);

Step 2: Insert Record

insert into teradatapoint.employee values (101,'Kalyan Roy','9620139678','M','HR');
insert into teradatapoint.employee values (102,'Rajesh Sharma','9611895588','M','ADMIN');
insert into teradatapoint.employee values (103,'Rupali Sharma','8884692570','F','SALES');
insert into teradatapoint.employee values (104,'Dipankar Sen','9957889640','M','HR');
insert into teradatapoint.employee values (105,'Sunitha Rai','9742067708','F','SALES');
insert into teradatapoint.employee values (106,'Parag Barman','8254066054','M','MAKETING');
insert into teradatapoint.employee values (107,'Vinitha Sharma','9435746645','F','ADMIN');
insert into teradatapoint.employee values (108,'Abhishek Saha','9850157207','M','SALES');
insert into teradatapoint.employee values (109,'Rushang Desai','9850157207','M','SALES');
insert into teradatapoint.employee values (110,'Arvin Kumar','8892340054','M','ADMIN');

Step 3: You can use count(distinct) to find out the number of distinct departments in the employee table as below.

SELECT COUNT(distinct department)
FROM Teradatapoint.employee;

Query completed. One row found. One column returned.

Total elapsed time was 1 second.

Result:

Example 3: There are occasions when we want to count all the distinct values. For instance, count the number of distinct genders in the Customer table. Here, the SQL Select statement must combine different clauses with the SQL Count function. In order to first determine the distinct gender values and utilise count function on it, we will use the inner select query.

SELECT Count(*) As DistinctGender FROM (SELECT DISTINCT CutomerGender FROM Customer);

SQL Distinct Values with Count:

What if we wanted to locate a customer's gender and how many there were? Using group by clause with distinct clause and count function, we can accomplish this.

SELECT DISTINCT CustomerGender, Count(*) as Occurs FROM Customer GROUP BY CustomerGender;

Result:

CustomerGender	Occurs
      M	          2
      F	          1

The SQL Distinct clause with examples is now complete. Please leave a comment if you have a specific scenario in mind, and I'll do my best to answer with a solution.

Example 4: You’d like to count how many different non-NULL values there are in a given column.

Our database has a table named customer with data in the following columns: id, first_name, last_name, and city.

id	first_name	last_name	city
1	 John	         Williams	 Chicago
2	 Tom	         Brown	         Austin
3	 Lucy	         Miller	         Chicago
4	 Ellie	         Smith	         Dallas
5	 Brian	         Jones	         Austin

Let’s find the number of different (and non-NULL) cities.

SELECT COUNT( DISTINCT city) as cities
FROM customer;

This query returns number of cities where customers live:

Discussion: Simply mark the column you send to the COUNT function as DISTINCT to count the number of distinct values that are kept in a specific column. COUNT returns the quantity of values in a given column when it is given a column. This only yields the number of distinct (and non-NULL) values when combined with DISTINCT.

Example 5:

Step 1: Create Table:

CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 

Step 2: Insert Records

INSERT INTO student VALUES 
  ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
  ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
  ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
  ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);

Step 3: Display Records:

SELECT COUNT(*) FROM student;

Result:

Step 4: Query:

SELECT COUNT(DISTINCT (name)) FROM student;

Output:

Example 6: For example, for the following records:

• Record 1: Size=small, Color=red
• Record 2: Size=small, Color=blue
• Record 3: Size=small, Color=red
• Record 4: Size=small

The following statement returns for each size the number of different values for the Color attribute:

RETURN result AS 
SELECT COUNTDISTINCT (Color) as Total 
GROUP BY Size

Result:

There is only one group and only one record because every single record has the same value for Size. Since the Color attribute has two distinct values red and blue for this group, the value of Total is 2.

Example 7: COUNTDISTINCT should be used with caution on the corpus. For instance, consider this:

The query SELECT COUNTDISTINCT(multiassign_attr) AS n FROM AllBaseRecords will de-multiassign before aggregating, hence the result will not be accurate.

To do this correctly, group by the attribute, then count the outcomes:

The correct way to do this is to group by the attribute, then count the results:

DEFINE a AS SELECT 1 AS n
FROM AllBaseRecords
GROUP BY multiassign_attr; 

RETURN b AS 
SELECT COUNT(n) AS n 
FROM a

The distinct in this table is spread over numerous columns, so count how many distinct items there are.

Example 1:

Method-1

Using a derived table (subquery):

You can simply create a select distinct query and wrap it inside of a select count(*) sql, like shown below:

SELECT COUNT(*) 
FROM (
SELECT DISTINCT DocumentId, DocumentSessionId
FROM Table
) AS internalQuery

Method-2

Using Concatenated columns:

A simpler method would be to use concatenated columns.

SELECT COUNT(DISTINCT(CONCAT(DocumentId,DocumentSessionId))) FROM Table;

Method-3

If performance is a factor:

Consider adding a column (physicalize) that concatenates all the columns and performs a hash on it to the table itself if you find yourself doing this frequently over huge tables.

Eg: you can add a new column to the table and store

MD5(CONCAT(DocumentId,DocumentSessionId))

Hence moving on, you can easily bank on distinct in this new column.

To enhance performance, you might even think about making indexes and/or computing statistics on this new column.

Example 2: With numerous columns, we cannot simply utilise the COUNT DISTINCT method in SQL. The following error message appears.

SQL Count Distinct function incorrect syntax error:

The SQL DISTINCT function records can be obtained using a temporary table, and the row counts can be verified using count(*).

SELECT DISTINCT City, State
into #Temp
FROM Location;
Select count(*) from #Temp

When we combine columns to obtain different values, if any of the columns have NULL values, the combination also qualifies as a singular one for the SQL Server.

Let's add more records to the location table to confirm this. In this query, no state was specified.

Insert into location values('Gurgaon','')
Insert into location(city)values('Gurgaon')

Let’s look at the location table data.

Re-run the query to get distinct rows from the location table.

SELECT distinct City, State
FROM Location;

The combination of City and State with blank or NULL values is still included in the output, as can be seen.

The result shows a 4 row count.

With SQL's COUNT DISTINCT function, you can also see the sixth row's count.

Example 3: Get the distinct count of instances of the concatenated text, then concatenate the columns to run as a single query.

SELECT count(DISTINCT concat(DocumentId, DocumentSessionId)) FROM DocumentOutputItems;

In MySQL you can do the same thing without the concatenation step as follows:

SELECT count(DISTINCT DocumentId, DocumentSessionId) FROM DocumentOutputItems;

When utilising the SQL SUM() function, the SUM of values for a field or column of a SQL table can be stored in a variable or temporary column known as an alias. The SQL COUNT() function can also be utilised using the same strategy.

To calculate the number of employees in each department, use the WITH clause. To calculate the employees SUM(...) of as many departments as required, write the basic SELECT query.

The number of rows that meet a specific condition is returned by the COUNT() method.

The sum of the numerical values in a table column is returned by the SUM() function.

The NULL values have no effect.

Example 1: You can see how to use many columns in the example below. You may count on orderDate and count_big on subtotal in this situation, and you can also view a variety of different aggregates like the sum of the subtotal.

SELECT COUNT(DISTINCT YEAR(SOH.OrderDate)) NO_Of_Distinct_Years, 
   COUNT_BIG(SOH.SubTotal) Total_No_Of_Records, 
   SUM(SOH.SubTotal) AS TotalSales
FROM sales.SalesOrderHeader SOH
   JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId;

Example 2: To calculate the number of employees in each department, use the WITH clause. To calculate the employees SUM(...) of as many departments as desired, write the basic SELECT query.

Learn how many employees work in both department 1 and department 3.

Step 1: Create Table

create table employee ( 
  id integer primary key, 
  dept_no integer default 1 
  -- more fields ... 
); 

Step 2: Insert Record

insert into employee (id, dept_no) values (1, 1); 
insert into employee (id, dept_no) values (2, 1); 

insert into employee (id, dept_no) values (3, 2); 

insert into employee (id, dept_no) values (4, 2); 
insert into employee (id, dept_no) values (5, 2); 

insert into employee (id, dept_no) values (6, 3);

Step 3:

with dept_emps AS ( 
   select dept_no, count(dept_no) as dept_count 
   from employee 
   group by dept_no 
) 
select sum(dept_count) 
from dept_emps 
where dept_no in(1, 3); 

Example 3: To get SUM of total number of records in 'customer' table, the following SQL statement can be used:

Sample table: customer

CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE |
 C00013    | Holmes      | London      | London       | UK           |     2 |       
 C00001    | Micheal     | New York    | New York     | USA          |     2 |     
 C00020    | Albert      | New York    | New York     | USA          |     3 |     
 C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     
 C00024    | Cook        | London      | London       | UK           |     2 |

Here is the Code:

SELECT SUM(GRADE)
FROM(SELECT COUNT( * ) AS Total
FROM customer); 

Main Article :- Sql difference between COUNT() and COUNT(DISTINCT) Functions

In SQL Server, there are two aggregate functions: count(All) and count(Distinct).

There is a distinction between those two COUNT() function iterations. When counting, COUNT(column_name) will take duplicate values into account. COUNT (DISTINCT column_name) will, on the other hand, only count distinct (unique) rows in the specified column.

1. The SQL Server Count() Function

When a where condition is combined with the Count function in a select statement, the number of rows is counted using the COUNT() function. The amount of rows in the table that meet the where condition will be returned. The Count function will not count NULL values.

Example :

SELECT  [EmpID]  
      ,[EmpName]  
      ,[EmpSalary]  
  FROM [master].[dbo].[Employee]  
  Go  
  select Count(empname) as CountResult from [Employee] 

Count(All ColumnName) Function: Similar to Count is the COUNT(All ColumnName) function (ColumnName). Additionally, it yields the same outcome as Count (ColumnName).

Example 1:

SELECT  [EmpID]  
      ,[EmpName]  
      ,[EmpSalary]  
  FROM [master].[dbo].[Employee]  
  Go  
  select Count(All empname) as CountResult from [Employee] 

2. Count(Distinct ColumnName) Function

Before the function count is used, or if you want to count the number of unique rows in the database, all duplicate values are removed when the DISTINCT keyword is used. Rows without values in the column are not counted by this version of the count function.

Example 2:

SELECT  [EmpID]  
      ,[EmpName]  
      ,[EmpSalary]  
  FROM [master].[dbo].[Employee]  
  Go  
  select Count(Distinct empname) as CountResult from [Employee] 

Example 3: Let’s check the result by looking at the entire orders table:

order_id	customer_id	order_price	order_date
OR2020-01	   CU108	15487	        2020-01-08
OR2020-28	   CU149	15487	        2020-01-14
OR2020-12	   CU108	12549.22	2020-01-09
OR2020-91	   CU012	542.55	        NULL
NULL	           CU092	1327.85	        NULL
OR2020-112	   CU049	15000	        2020-02-28
OR2020-213	   CU052	150	        2020-03-12
OR2020-213	   CU052	200	        2020-03-12

Using COUNT:

Maybe COUNT (column_name) will work if you want to count the number of customers who have placed an order. Let's test out this easy code:

SELECT COUNT (customer_id) AS number_of_customers
FROM orders;

Result:

You’re familiar with this one; I’ve already used the COUNT(column name) function. This time it counts all rows in the column customer_id, with the result being shown in the column number_of_customers.

Using COUNT(DISTINCT):

There are eight rows, but is this really the number of the customers? Notice that the customers CU108 and CU052 appear twice. If I want the real number of customers, then I need to count every customer only once. How can I do this? By using COUNT(DISTINCT customer_id):

SELECT COUNT(DISTINCT customer_id) AS number_of_customers
FROM orders;

This query will also count records in the customer_id column, but only once for each customer. This is because the phrase DISTINCT was used.

Result:

To produce a count of only the items that meet a predefined condition, CASE can be combined with SUM.

Example 1: Use MySQL's CASE WHEN clause for this and the COUNT() method's CASE condition to count. Let's start by making a table.

Step 1: Create Table

create table DemoTable1374
   (
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name varchar(20),
   Score int
   );

Step 2: Insert some records in the table using insert command:

insert into DemoTable1374(Name,Score) values('Chris',45);
insert into DemoTable1374(Name,Score) values('David',78);
insert into DemoTable1374(Name,Score) values('Bob',45);
insert into DemoTable1374(Name,Score) values('Mike',75);
insert into DemoTable1374(Name,Score) values('Carol',45);
insert into DemoTable1374(Name,Score) values('Adam',89);

Step 3: Display all records from the table using select statement:

select * from DemoTable1374;

Result:

  Id   Name    Score 
  1   Chris    45 
  2   David    78 
  3   Bob      45 
  4   Mike     75 
  5   Carol    45 
  6   Adam     89 

Step 4: Following is the query for CASE WHEN to set condition and count:

select count( case when Score=45 then 1 else NULL end) as  SpecificCondition from DemoTable1374;

Result:

Example 2: Let's say you want to know the total number of products that have been classified as "Expensive," given this table ItemSales:

Id	ItemId	Price	PriceRating
1	100	34.5	EXPENSIVE
2	145	2.3	CHEAP
3	100	34.5	EXPENSIVE
4	100	34.5	EXPENSIVE
5	145	10	AFFORDABLE

Query :

SELECT COUNT(Id) AS ItemsCount,
  SUM ( CASE 
   WHEN PriceRating = 'Expensive' THEN 1
  ELSE 0
   END
   ) AS ExpensiveItemsCount
FROM ItemSales 

Result:

ItemsCount	ExpensiveItemsCount
5	                3

Alternative:

SELECT COUNT(Id) as ItemsCount,
  SUM (
   CASE PriceRating 
   WHEN 'Expensive' THEN 1
   ELSE 0
   END
   ) AS ExpensiveItemsCount
FROM ItemSales

Use GROUP BY clause and COUNT() function for this.

Syntax:

SELECT yourColumnName1,yourColumnName2,..N,
COUNT(*) as anyAliasName FROM yourTableName
GROUP BY yourColumnName1,yourColumnName2;

Example 1:

Step 1: let us create a table. The query to create a table is as follows:

create table GroupAndCountByDate
   (
   Id int NOT NULL AUTO_INCREMENT,
   TripDate date,
   ShopId int,
    PRIMARY KEY(Id)
   );

Step 2: Insert Records

Now you can insert some records in the table using insert command. The query is as follows:

insert into GroupAndCountByDate(TripDate,ShopId) values('2019-01-31',10);
insert into GroupAndCountByDate(TripDate,ShopId) values('2019-02-01',15);
insert into GroupAndCountByDate(TripDate,ShopId) values('2019-01-31',10);
insert into GroupAndCountByDate(TripDate,ShopId) values('2019-02-01',15);
insert into GroupAndCountByDate(TripDate,ShopId) values('2019-03-23',20);
insert into GroupAndCountByDate(TripDate,ShopId) values('2019-04-21',25);
insert into GroupAndCountByDate(TripDate,ShopId) values('2019-03-13',20);
insert into GroupAndCountByDate(TripDate,ShopId) values('2019-04-06',25);

Step 3: Display all records from the table using select statement. The query is as follows:

select *from GroupAndCountByDate;

Output:

 Id  TripDate    ShopId 
  1  2019-01-31      10 
  2  2019-02-01      15 
  3  2019-01-31      10 
  4  2019-02-01      15 
  5  2019-03-23      20 
  6  2019-04-21      25 
  7  2019-03-13      20 
  8  2019-04-06      25 

Step 4: Here is the query to GROUP and count by date:

select TripDate,ShopId,COUNT(*) as TOTALTRIP
 from GroupAndCountByDate
 group by TripDate,ShopId;

Output:

 TripDate       ShopId  TOTALTRIP 
 2019-01-31       10         2 
 2019-02-01       15         2 
 2019-03-23       20         1 
 2019-04-21       25         1 
 2019-03-13       20         1 
 2019-04-06       25         1 

Example 2: When the column we want to group by in Microsoft SQL is a date time, how can we group by a date:

For instance, your database contains a column called Date_Added, and you want to know how many rows were created for each date (or in my example below, TrcDateTime).

Method 1:

select DATEADD(dd, 0, DATEDIFF(dd, 0, trcDateTime)) as Date, 
count(*) as count 
from trace 
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, trcDateTime))

The above provides the accurate counts but an unsightly Date because it displays the time as 00:00:00.00.0000 We can transform the date into a string to get away of that.

Method 2:

select LEFT(CONVERT(VARCHAR, DATEADD(dd, 0, DATEDIFF(dd, 0, trcDateTime)), 120), 10)  as Date, 
count(*) as count
from trace
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, trcDateTime))

Example 1: COUNT is likely the most popular distinct aggregate (DISTINCT). It resembles the windowed function DENSE_RANK() in certain ways. We can use DENSE_RANK() as a windowed distinct count since, unlike ROW_NUMBER(), it only increases the row counter when the ordering column(s) genuinely change from one row to the next:

SELECT Part, CountCol, ID,
    --- Windowed MAX() on the DENSE_RANK() column:
    MAX(_dense_rank) OVER (
    PARTITION BY Part) AS CountDistinct
FROM (
   SELECT *,
   --- DENSE_RANK() on the CountCol column:
   DENSE_RANK() OVER (
   PARTITION BY Part
   ORDER BY CountCol) AS _dense_rank
   FROM #CountDistinct
   ) AS sub;

The segment and sequence project is where the DENSE_RANK() calculation is displayed (top-center). Table Spool and Stream Aggregate are two ways that the windowed MAX() presents itself. The query is quite effective because it is non-blocking and doesn't need a memory permit.

Example 2: Count(*) returns the overall number of input rows, while count(f1) returns the rows particular to the window, which do not support DISTINCT or ORDER BY. However, you may simulate COUNT(DISTINCT) with two DENSE_RANKs: DENSE_RANK() over (partition by id, trxn_category order by id ASC)- DENSE RANK() over (partition by id, trxn_category order by id DESC) or a nested MAX(DENSE_RANK):

SELECT
   ANY_VALUE(employee_name) AS `employee_name`,
   DATE(created_at) AS `shift_date`,
   COUNT(*) OVER (PARTITION BY ANY_VALUE(created_at), ANY_VALUE(shift_id)) AS `shifts_on_day_1`,​
    (
     dense_rank() over (partition by ANY_VALUE(created_at) order by ANY_VALUE(shift_id) asc) +
     dense_rank() over (partition by ANY_VALUE(created_at) order by ANY_VALUE(shift_id) desc) - 1
    ) as `shifts_on_day_2`
​FROM scores
    GROUP BY employee_name, DATE(created_at);

Any row with the date 2020-04-01 should have shifts_on_day set to 1, and rows with the date 2020-04-02 should have shifts_on_day set to 2.

Although there are millions of entries in the table and only a few hundred are returned by the query, I have thought about utilising a correlated subquery, but it would be a performance nightmare.

Update: In my opinion, the fact that the query already has a group by makes window functions unnecessary. To obtain the average_score of each employee on a given day, all the data must be included in a single query. I can just COUNT(*) to get the final score for each employee.

Example 3: When there are ties between values, RANK returns duplicate values in the ranking sequence, whereas DENSE_RANK returns ranking values without gaps. Rows with ties still have equal values, but the ranking of the rows now shows the clusters of rows with equal values in the ordering column rather than the individual rows' places. The first row's rank is still 1, the second row's rank is still 2, and so are the ranks of the third and fourth rows, just like in the RANK example, where the rows ordering column values are 10, 20, 20, 20, 30. However, the final row is 3 and not 5.

DENSE_RANK is computed through a syntax transformation, as well.

DENSE_RANK() OVER ws

is equivalent to:

COUNT ( DISTINCT ROW ( expr_1, . . ., expr_n ) )
 OVER ( ws RANGE UNBOUNDED PRECEDING )

The list of value expressions in the sort specification list of window w1 is represented in the example above by expr_1 through expr_n.

In general, it takes longer to compute a DISTINCT aggregate than other aggregates. Additionally, a single DISTINCT aggregate-only query utilises less resources than a query that utilizes many DISTINCT aggregates.

Example 1:

The following query returns the number of distinct values in the primary_key column of the date_dimension table:

SELECT COUNT (DISTINCT date_key) FROM date_dimension;

Output:

Example 2: This example returns all distinct values of evaluating the expression x+y for all inventory_fact records.

SELECT COUNT (DISTINCT date_key + product_key) FROM inventory_fact;

Output:

Example 3: You can create an equivalent query using the LIMIT keyword to restrict the number of rows returned:

SELECT COUNT(date_key + product_key) FROM inventory_fact GROUP BY date_key LIMIT 10;

Output:

Example 4: This query returns the number of distinct values of date_key in all records with the specific distinct product_key value.

SELECT product_key, COUNT (DISTINCT date_key)  FROM  inventory_fact 
GROUP BY product_key LIMIT 10;

Output:

 product_key | count 
-------------+-------
           1 |    12
           2 |    18
           3 |    13
           4 |    17
           5 |    11
           6 |    14
           7 |    13
           8 |    17
           9 |    15
          10 |    12

Example 5: This query counts each distinct product_key value in inventory_fact table with the constant 1.

SELECT product_key, COUNT (DISTINCT product_key) FROM inventory_fact 
GROUP BY product_key LIMIT 10; 

Output:

 product_key | count 
-------------+-------
           1 |     1
           2 |     1
           3 |     1
           4 |     1
           5 |     1
           6 |     1
           7 |     1
           8 |     1
           9 |     1
          10 |     1

Example 6: For all records with the specified product_key value, this query chooses each unique date key value and counts the number of unique product_key values. The results are then grouped by date_key after adding the qty_in_stock values in all entries with the corresponding product_key value.

SELECT date_key, COUNT (DISTINCT product_key), SUM(qty_in_stock) FROM inventory_fact 
GROUP BY date_key LIMIT 10;

Output:

 date_key | count |  sum   
----------+-------+--------
        1 |   173 |  88953
        2 |    31 |  16315
        3 |   318 | 156003
        4 |   113 |  53341
        5 |   285 | 148380
        6 |    84 |  42421
        7 |   241 | 119315
        8 |   238 | 122380
        9 |   142 |  70151
       10 |   202 |  95274

Example 7: This query selects each distinct product_key value and then counts the number of distinct date_key values for all records with the specific product_key value. It also counts the number of distinct warehouse_key values in all records with the specific product_key value.

SELECT product_key, COUNT (DISTINCT date_key), COUNT (DISTINCT warehouse_key) FROM inventory_fact
GROUP BY product_key LIMIT 15;

Output:

 product_key | count | count 
-------------+-------+-------
           1 |    12 |    12
           2 |    18 |    18
           3 |    13 |    12
           4 |    17 |    18
           5 |    11 |     9
           6 |    14 |    13
           7 |    13 |    13
           8 |    17 |    15
           9 |    15 |    14
          10 |    12 |    12
          11 |    11 |    11
          12 |    13 |    12
          13 |     9 |     7
          14 |    13 |    13
          15 |    18 |    17

Example 8: This query selects all records with the specified product_key value, counts the number of records with the specified date_key and warehouse_key values, and then adds all the qty_in_stock values in records with the specified product_key value. The number of product_version values found in records containing the specified product_key value is then returned.

SELECT product_key, COUNT (DISTINCT date_key),
  COUNT (DISTINCT warehouse_key),
  SUM (qty_in_stock),
  COUNT (product_version)
  FROM inventory_fact GROUP BY product_key LIMIT 15;

Output:

 product_key | count | count |  sum  | count 
-------------+-------+-------+-------+-------
           1 |    12 |    12 |  5530 |    12
           2 |    18 |    18 |  9605 |    18
           3 |    13 |    12 |  8404 |    13
           4 |    17 |    18 | 10006 |    18
           5 |    11 |     9 |  4794 |    11
           6 |    14 |    13 |  7359 |    14
           7 |    13 |    13 |  7828 |    13
           8 |    17 |    15 |  9074 |    17
           9 |    15 |    14 |  7032 |    15
          10 |    12 |    12 |  5359 |    12
          11 |    11 |    11 |  6049 |    11
          12 |    13 |    12 |  6075 |    13
          13 |     9 |     7 |  3470 |     9
          14 |    13 |    13 |  5125 |    13
          15 |    18 |    17 |  9277 |    18

Example 9:

The following example returns the number of warehouses from the warehouse dimension table:

SELECT COUNT(warehouse_name) FROM warehouse_dimension;

Output:

Example 10: This next example returns the total number of vendors:

SELECT COUNT(*) FROM vendor_dimension;

Output:

Method 1:

SQL's COUNT() function by default includes duplicate values when used to a column. In essence, it totals all rows for which the column contains a value.

The DISTINCT clause of the COUNT() function can be used if you just wished to count the distinct values in a column.

Yes, we may use DISTINCT to ignore duplicate records in COUNT. The syntax is as follows:

select count(distinct yourColumnName) from yourTableName;

The number of rows in MySQL is shown by the COUNT() function. To retrieve the count of just unique rows, use DISTINCT to avoid duplicate rows.

Example 1: This will return 22, the number of distinct category values.

SELECT COUNT(DISTINCT category)
FROM fake_apps;

Example 2:

Step 1:

Let us first create a table:

create table DemoTable
(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   FirstName varchar(10)
);

Step 2: Following is the query to insert some records in the table using insert command:

insert into DemoTable(FirstName) values('Larry');
insert into DemoTable(FirstName) values('John');
insert into DemoTable(FirstName) values('Sam');
insert into DemoTable(FirstName) values('John');
insert into DemoTable(FirstName) values('John');
insert into DemoTable(FirstName) values('Larry');
insert into DemoTable(FirstName) values('Mike');
insert into DemoTable(FirstName) values('Robert');
insert into DemoTable(FirstName) values('Carol');
insert into DemoTable(FirstName) values('Mike');

Step 3: Following is the query to display records from the table using select command:

mysql> select *from DemoTable;

Output:

  Id   FirstName 
  1      Larry     
  2      John      
  3      Sam       
  4      John      
  5      John      
  6      Larry     
  7      Mike      
  8      Robert    
  9      Carol     
 10      Mike   

Step 4: Following is the query to ignore duplicate rows in COUNT and get the count of only unique rows:

select count(distinct FirstName) from DemoTable;

Result:

Method 2:

There are at least two different types of SQL queries you can use to discover duplicate values in your database table, depending on the result set you want to produce:

• Create all rows having a duplicate value in a column.
• Determine the number of rows that include a column with a duplicate value.

Consider the following data in your members table as an example. The only distinct rows in the table are the ones that have been highlighted:

You have two options for generating all rows with a duplicate value in a column:

• Using INNER JOIN subquery
• Using WHERE ... IN subquery

You must create a subquery that only returns the nation value that has appeared more than once for both of the aforementioned techniques.

Example 1: The subquery will be a SELECT statement with a GROUP BY and HAVING clause as follows:

SELECT country
FROM members
GROUP BY country
HAVING COUNT(country) > 1

Result:

+---------------+
| country       |
+---------------+
| United States |
| Canada        |
| Japan         |
+---------------+

Example 2: You can make a SELECT statement that joins with a temporary table produced by a query using the INNER JOIN clause as seen below:

SELECT id, first_name, members.country
FROM members
INNER JOIN(
  SELECT country
  FROM members
  GROUP BY country
  HAVING COUNT(country)>1
) AS temp_tbl WHERE members.country = temp_tbl.country;

Output:

+----+------------+---------------+
| id | first_name | country       |
+----+------------+---------------+
|  1 | Jackson    | United States |
|  2 | Sarah      | Canada        |
|  3 | Akihiko    | Japan         |
|  5 | Peter      | Canada        |
|  6 | Yuko       | Japan         |
|  7 | Devon      | United States |
|  8 | Sasuke     | Japan         |
+----+------------+---------------+

As you can see, all duplicate rows are displayed in the result set above.

Example 3: Besides using INNER JOIN clause, you can also use the WHERE ... IN clause to produce the same result as follows:

SELECT id, first_name, country
FROM members
WHERE country IN (
  SELECT country
  FROM members
  GROUP BY country
  HAVING COUNT(country) > 1
);

By using the WHERE... IN clause to filter the rows, MySQL will only display those where the value of the country column coincides with a value in the IN clause.

This has the benefit of returning a count of 0 even if there are no activities of that type for that time period, unlike grouping by activities, which returns a count of 1.

Example 1: This function is used to count all of the values in the dataframe as well as the values in a specific column.

I have a table of students:

id | age
--------
0  | 25
1  | 25
2  | 23

I want to query for all students, and an additional column that counts how many students are of the same age:

id | age | count
----------------
0  | 25  | 2
1  | 25  | 2
2  | 23  | 1

efficient way to do this,

SELECT age, count(age) 
  FROM Students 
 GROUP by age

If you need the id as well you could include the above as a sub query like so:

SELECT S.id, S.age, C.cnt
  FROM Students  S
  INNER JOIN (SELECT age, count(age) as cnt
  FROM Students 
  GROUP BY age) C ON S.age = C.age;

Example 2: Naturally, this won't produce rows for time periods in which there were no activity of any kind. Use a left join with a table that has a list of all the potential time segments if you require that.

SELECT time,
       activities,
       COUNT(*)
FROM table
GROUP BY time, activities;

This section will discuss select statements that utilize the HAVING clause and have a count of more than one.

Syntax:-

SELECT <your_column_name> 
FROM <your_table> GROUP BY <your_column_name> HAVING COUNT <another_column_name>

Example 1:-

Get the information about the customers who used more than one unique shopping method (who shopped online and at the shop).

SELECT cd1.*
FROM
    customer_data cd1
WHERE
    cd1.customer_id IN (SELECT 
     customer_id
     FROM
     customer_data
     GROUP BY customer_id
        HAVING COUNT(DISTINCT mode_of_shopping) > 1)
ORDER BY customer_name;

Explanation:-

• The above query can be divided into two parts inner-query and outer-query.
• The inner-query will get all the customer_id from customer_data table Grouped By customer_id and Having distinct mode_of_shopping > 1
• The outer-query will then get all the data corresponding to customer_id, matching with customer_id in inner-query.
• In the output, we can see that only customer_id -> 2, 3 and 5 are there in the result. Though customer_id=4 also has more than one mode_of_shopping, still is not included in the results as mode_of_shopping is not distinct. See the below image (figure 3) which shows data corresponding to customer_id=4.

Example 2: Let's now look at how to use EXISTS' select where count larger than one for any column.

Get all the information about customers who visited the store and/or shopped online. Look at the following question for the answer.

SELECT cd1.*
FROM
    customer_data cd1
WHERE 
    EXISTS( SELECT 
    *
    FROM
    customer_data cd2
    WHERE
    cd1.mode_of_shopping != cd2.mode_of_shopping
    AND cd1.customer_id = cd2.customer_id)
ORDER BY cd1.customer_id;

Explanation:-

• Subquery and EXISTS are being used in this case. When we use the MySQL function EXISTS with a sub-query, it will only return the rows if the Exists sub-query returns TRUE values, and vice versa.
• The Exists function will return the rows with matching customer id and non-matching mode of shopping because the sub-query is TRUE in our solution. Get all the information in the outer query that corresponds to the results of the inner sub-query for Exists.

The COUNT command counts the number of records that are present in a specific field.

Our data can be described using several groupings by using the COUNT() function along with GROUP BY. A column containing multiple instances of the same value will be considered as one group.

In order to create aggregate metrics, the GROUP BY clause in SQL is used to aggregate (group) data together in groups. The SQL aggregate procedures COUNT(), SUM(), AVG(), MIN(), and MAX (). For numerous rows, these functions produce a single value. Because it might not always be possible for you to look through each entry to develop insights, aggregation is vital.

A few instances of aggregation include grouping clients according to their unique demands and activities (segmentation), categorising products, and categorising different individuals according to their income.

Each circle in the graphic below has a certain hexagon associated to it. There are some blue circles, some green circles, and some purple circles. The hexagons are grouped based on the circles to which they are attached using the GROUP BY function. Later, the COUNT function is employed to determine how many hexagons are connected to a specific circle.

Example 1: Suppose there is a table which stores the records of immigrants. It stores their names, IDs and nationality.

Table : Immigrants

ID	Name	  Country
123	Rooney	  England
457	Nekham	  Australia
312	George	  USA
761	McKenzie  USA
478	McGrath	  Australia

Now, if this table is grouped by nationality and their IDs are to be counted and displayed, the code will be :

SELECT COUNT(id), nationality 
  FROM immigrants
 GROUP BY(nationality)

The above code correctly calculates the number of people belonging to each specific country.

Example 2: To get data of 'working_area' and number of agents for this 'working_area' from the 'agents' table with the following condition:

'working_area' should come uniquely, the following SQL statement can be used :

SELECT working_area, COUNT(*) 
FROM agents 
GROUP BY working_area;

Sample table : agents

| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO       |
| A007       | Ramasundar           | Bangalore          |       0.15 | 07725814763    |         
| A003       | Alex                 | London             |       0.13 | 07512458969    |         
| A008       | Alford               | New York           |       0.12 | 04425874365    |         
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 07745625874    |         
| A010       | Santakumar           | Chennai            |       0.14 | 00722388644    |         
| A012       | Lucida               | San Jose           |       0.12 | 04452981425    |

Example 3: To better understand this, let's use a COUNT() function example. Let's say you want to figure out how many products are in each product line.

SELECT product_line,
     COUNT(product_code)
FROM products
GROUP BY product_line;

Result:

product_line	COUNT(product_code)
Motorcycles	3
Classic Cars	2  

In this query, SQL first gets all the unique values for product_line as the first column, then counts the number of products (product_code) belonging to each of them. In short, SQL aggregates all rows by product line and then uses the aggregate function (COUNT() in this case) to calculate the number of products for each product line.

It's crucial to list all the columns you need in the SELECT portion of the query in the GROUP BY clause (apart from the aggregate function part). SQL throws an error if it doesn't. You can read this article to learn about typical GROUP BY clause mistakes.

The COUNT() function is present in the SELECT portion of the query in the examples I've provided so far in this post. However, COUNT() is a flexible function that can be used in a variety of contexts throughout the query.

The aggregate function filters groups based on conditions using the HAVING clause. Say, for example, that you only want the aggregated groups with counts greater than a particular limit to be returned.

Example 1: With our dataset, imagine you want to only look at those product lines with more than 2 products.

SELECT product_line,
       COUNT(product_code)
FROM products
GROUP BY product_line
HAVING COUNT(product_code)>2;

Output:

product_line	COUNT(product_code)
Motorcycles	    3

Similar to the previous query, this one combines rows based on unique values of product line and uses the COUNT() method to retrieve the number of products that are part of each product line. The "Classic Cars" product line (which contains only 2 products) does not appear in the results, nevertheless, as we have decided to keep only the product lines with more than two products.

Example 2:

Step 1: Create Database

Consider a table STUDENT having the following schema:

STUDENT (Student_id, Student_Name, Address, Marks) 
Student_id is the primary column of STUDENT table.

Step 2: Create Table

Let first create the table structure with CREATE Command in SQL:

CREATE TABLE STUDENT 
(STUDENT_ID NUMBER (4), 
STUDENT_NAME VARCHAR2 (20), 
ADDRESS VARCHAR2 (20), 
MARKS NUMBER (3), 
PRIMARY KEY (STUDENT_ID));

Step 3: Insert Records

Now, insert values into the table using INSERT INTO Command in SQL:

INSERT INTO STUDENT 
VALUES (100, ‘PUJA’, ’NOIDA’, 10); 

INSERT INTO STUDENT 
VALUES (101, ‘SUDO’, ’PUNE’, 30); 

INSERT INTO STUDENT 
VALUES (102, ‘BHALU’, ’NASHIK’, 40); 

INSERT INTO STUDENT 
VALUES (103, ‘CHETENA’, ’NOIDA’, 20); 

INSERT INTO STUDENT 
VALUES (104, ‘MOMO’, ’NOIDA’, 40);

Step 4: Display Records

Now display the content of STUDENT table:

SELECT * FROM STUDENT;

Output:

Student_id    Student_Name    Address    Marks
------------------------------------------------
100            PUJA            NOIDA      10
101            SUDO            PUNE       30
102            BHALU           NASHIK     40
103            CHETENA         NOIDA      20
104            MOMO            NOIDA      40

Print the marks and number of student having marks more than the average marks of student from NOIDA city.

Explanation: To get the average marks of student from NOIDA city we use this query:

SELECT AVG(MARKS) 
FROM STUDENT 
WHERE ADDRESS =’NOIDA’ 

Step 5: We use this above sub query using GROUP BY and HAVING clause:

SELECT MARKS, COUNT (DISTINCT STUDENT_ID) 
FROM STUDENT 
GROUP BY MARKS 
HAVING MARKS > (SELECT AVG(MARKS) 
    FROM STUDENT 
    WHERE ADDRESS = ’NOIDA’ ); 

In the above query we use GROUP BY MARKS means it cluster the rows with same Marks and we also use SELECT MARKS, COUNT(DISTINCT STUDENT_ID) which prints the Marks of each cluster and the count of rows of respective clusters i.e.,

Output:

MARKS    COUNT
10           1
20           1
30           1
40           2 

After that we use HAVING MARKS > (SELECT AVG(MARKS) FROM STUDENT WHERE ADDRESS =’NOIDA’), which is used to filter the result with condition that marks must be greater than the avg marks of student from Noida city i.e., more than (10+20+40) / 3 = 23.3.

Output:

MARKS       COUNT (DISTINCT STUDENT_ID)
30             1
40             2

The MySQL Count() method can be used to determine how many rows are there in a table or expression. We shall discuss the count() function with if in this article.

Gives the expression's TRUE value count. If there are no input rows or if the expression evaluates to FALSE across all rows, it returns 0.

Syntax:

SELECT [DISTINCT] COUNT([DISTINCT] IF(<condition>, <expression>, NULL)) AS alias_name FROM your_table_name;

Syntax shows that,

• The IF() function, which has a condition supplied, is a part of the COUNT() function.
• The count will be determined using the expression provided if the < condition> is true. If NULL is provided, the count() method returns NULL. If NULL is supplied to count(), it will return the number of null values in the column designated by your column name rather than the number of results.
• The keyword DISTINCT is optional.
• The name you give the count results is alias-name.
• The name of the table from which you wish to obtain the count is your_table_name..

Example 1:

Step 1: We are creating a table students_data followed by adding data to it.

CREATE TABLE students_data (
student_id INT AUTO_INCREMENT,
student_name VARCHAR(255),
student_address VARCHAR(255),
student_grade VARCHAR(255),
student_subject VARCHAR(255),
PRIMARY KEY (student_id)
);

Step 2: Insert Records

INSERT INTO students_data (student_name,student_address,student_grade,student_subject) 
VALUES("Gustav","56A Denmark","A","Physics"),
("Henric","255 USA","B","Geography"),
("Richa","78 India","C","Physics"),
("Margit","58 Canada","A","Physics"),    
("Rasmus","18 Libya","B","Physics"),
("Erick","43 Sweden","C","Geography"),
("Tanya","78 Singapore","D","Geography"),
("Monika","255 Italy","A","Chemistry"),
("Atharv","587 California","A","Chemistry"),    
("Eva","18 Singapore","D","Physics"),
("Joan","43 Germany","D","Physics"),
("Jacob","78 Singapore","C","Chemistry"),
("Thomas","258 Germany","C","Geography"),
("Rohit","899 Bangladesh","A","Geography");

Step 3: Display Records

Let us see what got into table students_data by executing:

SELECT * FROM students_data;

Since the data got created now, let us forge ahead by looking into the syntax of COUNT() with IF().

Example 2: View the following examples to gain a deeper understanding of the idea. To obtain the various counts from the table students_data based on conditions, we will examine three cases.

Get the count of subjects associated with Grade A :

SELECT DISTINCT
  COUNT(DISTINCT IF(student_grade = 'A',
   student_subject,
   NULL)) AS countOfSubjects
FROM
  students_data;

Output:

Explanation:-

The results demonstrate that the students received grades of "A" in three different areas.

Here, we're using the count() method to calculate the number of distinct subjects if the student grade is "A" on the students subjects column. If the grade is anything less than a "A," the subject count will not be determined.

Example 3:

SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;

Result:

+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3            | 4            |
+--------------+--------------+

SELECT x,
  COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;

Result:

+------+--------------+
| x    | num_negative |
+------+--------------+
| NULL | 0            |
| 0    | 1            |
| -2   | 1            |
| 3    | 1            |
| 4    | 0            |
| 5    | 0            |
| 6    | 1            |
| -7   | 2            |
| -10  | 2            |
+------+--------------+

Example 4: The standard language for storing, manipulating, and retrieving data in databases is called SQL (Structured Query Language) or similar. It is a typical upgrade carried out by businesses that experience Excel's limitations. The initial response is often to agree on a budget to store the data in a database and then use SQL to organise and manipulate the data in this database. Additionally, the language is quite commendable. When typing a straightforward SQL request, it almost feels like you are speaking a natural language.

Step 1: Create Table

Let’s start with creating a table with items:

CREATE TABLE table1 (
    Item varchar(255)   
);

Step 2: Insert Record

INSERT INTO table1
VALUES ('Item1');
INSERT INTO table1
VALUES ('Item2');
INSERT INTO table1
VALUES ('Item1');
INSERT INTO table1
VALUES ('Item3');
INSERT INTO table1
VALUES ('Item1');
INSERT INTO table1
VALUES ('Item4');
INSERT INTO table1
VALUES ('Item4');

Step 3: Display Record

SELECT*FROM TABLE1;

Then keep in mind that you frequently use the expression SELECT in SQL to retrieve information. In essence, SQL lets you experiment with data, tables, and relationships. Therefore, in order to SELECT it, you must thoroughly grasp "how I can receive this information":

SELECT “How I will get what I want” FROM “Were I have to apply the How”.

So in our case we will select the count of the case where the items of the column “Item” equals “Item1”.

Step 4: Let’s write the formula and then analyze it:

SELECT COUNT(CASE WHEN Item = 'Item1' THEN 1 END) FROM table1;

As a result, we SELECT the COUNT of the CASE WHEN ITEM='Item1' (CASE in SQL is a type of "IF" function). IF such is the case, we then count 1. And table 1 reflected this.

The month portion for a given date is returned by the MONTH() function (a number from 1 to 12).

To calculate the number of rows in a table for any date, use the to_char and count functions.

Example 1: Here is an example of counting rows by month:

select to_char(mydate,'YYYY-MM'),
   count(mydate)
from
   mytab
having
   count(mydate) > 1
group by
   mydate
order by
   to_char(mydate,'YYYY-MM') desc;

Example 2: You can alternatively use the MON date type, which displays the month name rather than the month number, to count by month:

select to_char(mydate,'YYYY-MON'),
   count(mydate)
from
   mytab
having
   count(mydate) > 1
group by
   mydate
order by
   to_char(mydate,'YYYY-MON') desc

Example 3: Our database has a table named watch with data in the columns id, name, and production_timestamp.

id	name	        production_timestamp
1	watch	        2019-03-01 11:45:23
2	smartwatch	2019-09-15 07:35:13
3	smartband	2019-09-22 17:22:05

You can use the DATE_TRUNC() function to group records in a table by month.

Here's the query you would write:

SELECT
       DATE_TRUNC('month',production_timestamp)
         AS  production_to_month,
       COUNT(id) AS count
FROM watch
GROUP BY DATE_TRUNC('month',production_timestamp);

Result:

production_to_month	    count
2019-03-01 00:00:00	    1
2019-09-01 00:00:00	    2

Discussion:

• If you wish to obtain a date or time from a PostgreSQL database with a certain level of accuracy, use the DATE_TRUNC() function. (We used month precision in our example.)
• Two arguments are required for this function. The date component specifier comes first (in our case, "month"). Keep in mind that the specifier must be contained in quotes because it is a string.
• The second argument is the timestamp value, which can be either the name of a timestamp column or an expression returning a timestamp value. (In our example, the production_timestamp field is used.)
• The timestamp is truncated to the specified precision using the function DATE_TRUNC() (in this case, the month). The production date for the smartwatch is 2019-09-15 07:35:13 in our database; when it is reduced to month precision, it becomes 2019-09-01 00:00:00. The day is shown as "01" and the time is zeroed out due to the month-level precision. (The timestamp's abbreviated date portions, such as days and months, are changed to ones.)
• In PostgreSQL, it's fairly usual to group records by month. In our example, each month's total for products is computed. (The number of products is counted using the COUNT() aggregate function.) You must utilize the GROUP BY clause when grouping records using an aggregate function. The columns or expressions used with the aggregate function should come after the GROUP BY clause in the SELECT statement. This is DATE_TRUNC("month", production_timestamp) in our example.

To perform this for multiple tables, use the UNION ALL.

Syntax:

select sum(variableName.aliasName)
from
   (
   select count(*) as yourAliasName from yourTableName1
   UNION ALL
   select count(*) as yourAliasName from yourTableName2
   ) yourVariableName;

Example 1: Let's use the aforementioned syntax. I'm using an example database with extra tables for this.

The two tables we are using are

• userdemo
• wheredemo

Here is the query to display all records of both the tables. The query is as follows to display records from table ‘userdemo’.

select *from userdemo;

Result:

+--------+----------+------------------+
| UserId | UserName | RegisteredCourse |
+--------+----------+------------------+
| 1      | John     | Java             |
| 2      | Larry    | C                |
| 3      | Carol    | C++              |
| 4      | Mike     | C#               |
+--------+----------+------------------+

The query is as follows to display records from table ‘wheredemo’.

select *from wheredemo;

Result:

+------+---------+
| Id   | Name    |
+------+---------+
| 101  | Maxwell |
| 110  | David   |
| 1000 | Carol   |
| 1100 | Bob     |
| 115  | Sam     |
+------+---------+

Here is the query to implement count(*) from both the above tables

select sum(tbl.EachTableCount)
   from(
   select count(*) as EachTableCount from userdemo
   UNION ALL
   select count(*) as EachTableCount from wheredemo
   )tbl;

Result:

The number of non-NULL items in the provided column will be counted by the COUNT() function (NULL fields will be ignored). We utilise the CASE to convert NULLs into values and values into NULLs because the COUNT (and other aggregation functions) will ignore NULL values.

Example 1: First, let us create a table and populate it with a few NULL Values.

Step 1: -- Create a Table

CREATE TABLE Table1 (Col1 INT);

Step 2: -- Insert Data

INSERT INTO Table1 VALUES (1), (2);
INSERT INTO Table1 VALUES (NULL),(NULL),(NULL);

In the above script, we have inserted 3 NULL values.

Step 3: Now run the following command to count all the NULL values from the table.

SELECT COUNT(Col1,0) CountCol
FROM Table1
WHERE Col1 IS NULL;

Even though we have three NULL values, you'll see when you look at the query's results that it indicates there are none. This is so that all aggregate values don't take into account NULL values. The aggregate function must be used after converting the NULL values to other values in order to count the NULL values, as seen in the script below.

SELECT COUNT(ISNULL(Col1,0)) CountCol
FROM Table1
WHERE Col1 IS NULL;

You'll see that the query now correctly displays NULL values when you run the script mentioned above. The reasoning behind excluding NULL values is straightforward: it prevents unknown or irrelevant values from having an impact on the aggregate's outcome.

Example 2: MySQL's CASE command can be used to count null values. Let's first construct a table and look at an example:

Step 1: Create Table

create table DemoTable
(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   FirstName varchar(20)
);

Step 2: Insert Data

Insert some records in the table using insert command:

insert into DemoTable(FirstName) values('John');
insert into DemoTable(FirstName) values(null);
insert into DemoTable(FirstName) values('');
insert into DemoTable(FirstName) values('Larry');
insert into DemoTable(FirstName) values('');
insert into DemoTable(FirstName) values(null);
insert into DemoTable(FirstName) values(null);
insert into DemoTable(FirstName) values('Bob');

Step 3: Display Data

Following is the query to display all records from the table using select statement:

select *from DemoTable;

Result:

 Id   FirstName 
 1     John      
 2     NULL      
 3             
 4     Larry     
 5             
 6     NULL      
 7     NULL      
 8     Bob       

Step 4: Here is the query to count Null values in MySQL:

select sum(case when FirstName IS NULL then 1 else 0 end) as NUMBER_OF_NULL_VALUE from DemoTable;

Result:

A subquery is a query that is nested inside of a select, insert, update, delete, or subquery.

Example 1: Count and subquery in Having clause

Step 1: Create Table

CREATE   TABLE MATCHES
   (MATCHNO        INTEGER      NOT NULL,
   TEAMNO         INTEGER      NOT NULL,
   EmployeeNO       INTEGER      NOT NULL,
   WON            SMALLINT     NOT NULL,
   LOST           SMALLINT     NOT NULL,
   PRIMARY KEY    (MATCHNO)            );

Step 2: Insert Data

INSERT INTO MATCHES VALUES ( 1, 1,   6, 3, 1);
INSERT INTO MATCHES VALUES ( 2, 1,   6, 2, 3);
INSERT INTO MATCHES VALUES ( 3, 1,   6, 3, 0);
INSERT INTO MATCHES VALUES ( 4, 1,  44, 3, 2);
INSERT INTO MATCHES VALUES ( 5, 1,  83, 0, 3);
INSERT INTO MATCHES VALUES ( 6, 1,   2, 1, 3);
INSERT INTO MATCHES VALUES ( 7, 1,  57, 3, 0);
INSERT INTO MATCHES VALUES ( 8, 1,   8, 0, 3);
INSERT INTO MATCHES VALUES ( 9, 2,  27, 3, 2);
INSERT INTO MATCHES VALUES (10, 2, 104, 3, 2);
INSERT INTO MATCHES VALUES (11, 2, 112, 2, 3);
INSERT INTO MATCHES VALUES (12, 2, 112, 1, 3);
INSERT INTO MATCHES VALUES (13, 2,   8, 0, 3);

Step 3:

SELECT   TEAMNO, COUNT(*)
FROM     MATCHES
GROUP BY TEAMNO
HAVING   COUNT(*) >= ALL
(SELECT   COUNT(*)
FROM     MATCHES
GROUP BY TEAMNO);

Result:

+--------+----------+
| TEAMNO | COUNT(*) |
+--------+----------+
|      1 |        8 |
+--------+----------+

drop table matches;

Example 2: Select the names of the publishers whose book count values in the library match the actual count of books.

SELECT pubname, bookcount
FROM library
WHERE (bookcount, pubnum) IN (SELECT COUNT(*), book.pubnum
  FROM book
  GROUP BY pubnum);

Because no rows are returned by the subquery for this publisher, the name of that publisher is not returned if the book count for that publisher in the library is 0.

The following table outlines how the result data format for a COUNT operation differs for ANSI and Teradata session modes. Teradata VantageTM SQL Functions, Expressions, and Predicates, B035-1145, contains more information.

IN this session mode … THE data type of the result for a COUNT operation is …

DECIMAL(p,0): where p represents the precision of the number.

If the DBS Control field MaxDecimal is set to any of the following values, then the value of p is 15.

The value of p is 31 if the DBS Control field MaxDecimal is set to 31.

The value of p is 38 if the DBS Control field MaxDecimal is set to 38.

Example 3: Another comparable SELECT query employs two unrelated subquestions to produce the same set of results, as shown below:

SELECT pub_name, book_count
FROM library
WHERE (book_count, pub_num) IN (SELECT COUNT(*), pub_num
  FROM book
  GROUP BY pub_num)
  OR NOT IN (SELECT book.pub_num
  FROM book
  GROUP BY pub_num)
  AND book_count = 0;

Example 4: The next SELECT statement employs a correlated subquery to produce the same accurate result as the first one, but it is simpler and more elegant.

SELECT pub_name, book_count
FROM library
WHERE book_count IN (SELECT count(*)
FROM book
WHERE book.pub_num = library.pub_num);