SQL PRACTICE: SQL SERVER - Total Count of Records

 Q1. How to find the total number of records (number of rows) in SQL?


N.B: I am using the following:

Database: MSSQL

SQL Dialect: T-SQL 


 I have a created an employee table as follows:

-- Create a SQL database

Create database SQL;


use sql;


-- Create an employee table

create table employee(

EmpId int,

Name varchar(20),

Salary int

);


-- Insert values to the employee table

insert into employee (EmpId, Name, Salary)

Values

(100, 'Genet', 40000),

(100, 'Genet', 40000),

(101, 'Mike', 80000),

(101, 'Mike', 80000),

(102, 'Teddy', 60000),

(102, 'Teddy', 60000),

(103, 'Mat', 100000);


                                                          Table1: Employee Table

Ans: I have a couple of options to find the total records of the given table: using count or without count function.

1. Count () Function

select count (*) as Total_Row_Count from employee;

Table2. Count method

2. Window function (Aka without count function)

with cte as(

select  *, 

ROW_NUMBER() over (order by empid) as row_num

from employee)

select max(row_num) as Total_Row_Count from cte;


Table3. Window function method

Comments

Post a Comment

Popular posts from this blog

SQL PRACTICE: SQL SERVER - DATEADD Function

Image
  Q12. Find the join date of employees   if they  join after 2 weeks of the hire date? N.B: I am using the following: Database: MSSQL SQL Dialect: T-SQL  Consider below table (Sql_data) as a source data. select * from Sql_data; Table 1. Source Data -- Find the join date of employees select      empid,  FirstName,  LastName, DOB, Hire_date, DATEADD(WK, 2, Hire_date) as Join_date, Salary from Sql_data; Table 2. Join Date Explanation :  DATEADD  function adds a number to a datepart and returns a modified  value based on a   specified  datepart  (Year, Month, Week, Day …..etc...).  Syntax: DATEADD (datepart, NumberToAdd, Date) Parameters: datepart -  It is part of the date to which the dateadd () function will add a number. It can be Year(YY, YYYY), Quarter(QQ, Q), month(MM,M), day(DD, D), …..etc....  In our case, we are going to use Week (WK) NumberToAdd -  It is an ...
Read more

SQL PRACTICE: SQL SERVER - Unique Records

Image
 Q2: How to select unique values in SQL? N.B: I am using the following: Database: MSSQL SQL Dialect: T-SQL  Below is an employee table given. It has 3 fields and 7 records.                                                       Table1: Employee Table To find unique values of the table, I am going to use three different ways:  1. Using distinct clause select distinct EmpId, Name, Salary from employee; Table2: Distinct Clause Method 2. Using Group by (Without using distinct keyword) select EmpId, Name, Salary from employee group by EmpId, Name, Salary; Table3. Group By Method 3. Using Row_Number Function select Empid, Name, Salary , ROW_NUMBER() over(partition by empid, Name,  Salary order by empid) as Row_Num from employee; Result: Table4. Row_Number Function Row_number is used to give a unique number for each row as you can see from ta...
Read more

SQL PRACTICE: SQL SERVER: DATEDIFF Function

Image
 Q11. Find the age of employees from the provided table using SQL? N.B: I am using the following: Database: MSSQL SQL Dialect: T-SQL  Consider below table (Sql_data) as a source data. select * from Sql_data; Table 1. Source Data -- Find the age of employees select      empid,  FirstName,  LastName, DOB,  DATEDIFF(YY, DOB, GETDATE()) as Age from Sql_data;                                                                      Table 2. Age of Employees Explanation : DATEDIFF function is used to return the date difference between two dates according to the date part specified (Year, Month, Day …..etc...).  Syntax: DATEDIFF(datepart, Date1, Date2) Parameters: datepart - Used to get the time difference between the two dates. It can be Year(YY, YYYY), Quarter(QQ , Q)...
Read more