Grouping by Multiple Columns in SQL

Grouping by Multiple Columns in SQL

I will explain this topic with an example.

Yesterday one of my colleague went for an Interview.

The Interviewer asked him that you have two tables Family and Members 

Family table has familyId,Headof the Family and FamilyAddress fields

whereas

In Child Table it has columns MemberID,FK_FamilyId,MemberName

so the Interviewers asked him  to calculate total no. of members for each family which includes HeadofFamily and familyaddress.

Below is the Table Structure with Data.

create table Family

(familyId nvarchar(50),

 HoF nvarchar(50),--HeadofFamily

 familyadd nvarchar(200)

 )

 create table Members

 (MemberId nvarchar(50),

  FK_FamilyId nvarchar(50),--Take values from Familyid from familytable

  MemberName nvarchar(50)

  )

--Family Info

insert into Family values(1,'rakesh','btm2'),('2','raghu','jpngr'),('3','vnk','mahalkshmi')

--FamilyMember Info.  

insert into members values('1','2','sumit'),('2','2','amit'),('3','2','mit'),('4','1','vikas'),('5','1','vijay'),('6','3','anil')

Now if you see in the family table Rakesh,Raghu and Vnk are the Head of Family.

The requirement was like TotalNo.ofMembers,HOF,FamilyADD

So below is the query using using Group by Various column.

  select COUNT(m.memberid)'No.ofMembers',f.HoF ,f.familyadd  from Members m join Family f

  on f.familyId =m.FK_FamilyId  group by f.familyId,f.HoF ,f.familyadd

No.ofMembers HoF familyadd

2              rakesh            btm2

3               raghu             jpngr

1               vnk                   mahalkshmi

Explanation-The query is giving the count of Members for each family using group by familyID In order to get  Headoffamily and Familyaddress the query has been grouped by Hof and FamilyAdd too.

Note if you will remove HOF and FamilyADD from group by clause it will throw an ERROR.

Msg 8120, Level 16, State 1, Line 2

Column 'Family.HoF' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The Reason for the error is every column written in Select query must be included in Group by clause when using select with aggregate function and group by clause, except the columnn which  is used in aggregate function ( min,max,count,avg...) .

Calculation on Numbers Resulting it as a Integer values not in decimal in SQL

Calculation on Numbers Resulting it as a Integer values not in decimal  in SQL

A comman mistake while performing calculation  in sql query  not getting exact output or Result

In order to know more about this topic run a simple query as below.

select 13/2

Output 6 

But not exact as the output should come as 6.5

Try to convert or cast it as float or decimal

select CAST((13/2) as float)

Output 6 

select convert(decimal(12,0),(13/2))

Output 6 

None of above query is giving exact result 

Now run this one

select cast(13 as float)/2

Output 6.5

select 13/cast(2 as float)

Output 6.5

The Reason for above 2 queries not giving exact output is we are getting the result and after that we were casting it to float of decmial values 

In last 2 query what we did is in the calculation time only we have casted or coverted the denominator or numerator into float so int divided by float of float divided by Int will result as Float.

Note : Float has higher preference then int .In order to check Datatype precedence go to msdn below link

http://technet.microsoft.com/en-us/library/ms190309.aspx

Concatenating Null values with a String or Number in SQL 2008

Concatenating Null values with a String or Number in SQL 2008 and 2012

Anything concatenate with NULL yields to output as NULL but in case when you want to concatenate NULL field with other but resulting NULL can hamper your output.

Take an example like you have a table where you have EmployeeFirst Name and EmployeelastName columns in the report and you want to show EmployeeFull Name as seprate column in the report

but some of employee dont have last Name so Concatenating Employee first and last will result employeefull name as null for employee who doesnt have last Name

below is the sample table with above example

create table Employee_NUll

(Empid int ,EmpFName nvarchar(50),EmpLName nvarchar(50))

Insert into Employee_NUll values(1,'Vijay','Rana')

,(2,'Amit',null),(3,'Suresh',null),(4,'Ravi','Rai')

select (EmpFName+EmpLName)EmpFullName,* from Employee_NUll

Output

EmpFullName Empid EmpFName EmpLName

VijayRana    1 Vijay Rana

NULL         2 Amit  NULL

NULL        3     Suresh NULL

RaviRai       4   Ravi Rai

If you see output the Employee full Name is coming NULL for employee having last name NULL So In order to handles this 

SET CONCAT_NULL_YIELDS_NULL Off;

select (EmpFName+EmpLName)EmpFullName,* from Employee_NUll

Output

EmpFullName Empid EmpFName EmpLName

VijayRana    1 Vijay Rana

Amit         2 Amit  NULL

Suresh        3     Suresh NULL

RaviRai       4   Ravi Rai

Other Methods to concatenate with NULL values

IsNUll function

select isnull(EmpFName+EmpLName,EmpFName)EmpFullName,* from Employee_NUll

Coalesce Function

select coalesce(EmpFName+EmpLName,EmpFName)EmpFullName,* from Employee_NUll

SQL 2012 has provided a new function to concat with NULL values

--This will work only in SQL 2012 and above.....

select concat(EmpFName+EmpLName,EmpFName)EmpFullName,* from Employee_NUll

Traversing in all Tables of Database in SQL

Traversing in all Tables of  Database in SQL 

In order to Traverse through all tables of SQL like you want to get Counts of all Records of Each table in a Database or suppose you want to delete data of all tables of a Database for these type of requirement where you want a looping in all tables sql has provided a system stored Procedure sp_MSForEachTable

1-To Get count of all tables of a database

Select your DB Run below query

--Count all Records of all Tables in Database TestingNew

exec sp_MSforeachtable 'select ''?''[TableName], count(*)[Count] from Testingnew.?'

2-To Delete Data of all Tables of a Database donot apply into your working Database create a seprate Database with sample tables to test this query.

Select your Testing DB Run below query

-- delete data of all tables

EXEC sp_MSForEachTable 'DELETE FROM Test.?'

This will delete data of all tables of a database

Explanation-sp_MSforeachtable  traverse through all tables there is a question mark in the queries which denotes all Tables 

NOTE: If you want to run delete all data query where tables have foriegn key dependencies then you have to disable all Constraints first.

Result of one Table into Another table in SQL

Result of one Table into Another table in SQL

Some times you have  requirement like you want to copy data of one table to another table or you want to have a new table like old table There are many ways to achieve this I will explain you all in each points with query and Scripts

Below is the script of table to be copied

--New table

CREATE TABLE [dbo].[testcopy](

 [column1] [nvarchar](50) NULL,

 [column2] [nvarchar](50) NULL

) ON [PRIMARY]

GO

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'5', N's')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'4', N'd')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'8', N'f')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'9', N'r')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'6', N't')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'8', N'y')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'7', N'h')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'3', N'u')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'6', N'i')

1-Creating a new table along with data using above table Script i.e. copying structure and data using query

Select * into NewTestCopy from Testcopy

--to create a temp table using above table

Select  * into #NewTestcopy from TestCopy

--try to create a temptable using Select into

Select * into @NewTestcopy from TestCopy

This will throw an error as Select * into doesn't work with table variables

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '@NewTestcopy '.

2-Copying data only from above table to another table or temp table or table variable

For this we will create a new table

CREATE TABLE [dbo].[testcopy1](

 [column1] [nvarchar](50) NULL,

 [column2] [nvarchar](50) NULL

) ON [PRIMARY]

GO

--Copying Data of testcopy to testcopy2

insert into testcopy1

select * from testcopy 

3-Copying data only of selected column from above table  to another table selected columns

insert into testcopy1(column1)

select column1 from testcopy 

4-If no. of Rows are less you can use GUI also to copy data from one table to another.

Right click -Sourcetable-Edit top 200 rows-Select All-Copy all data

Right click -Destination Table-Edit top 200 rows-Paste.

5-Using Import and Export wizard.

 selecting your source and Destination table or you can create Destination table Same as Source table along with data but same drawback that it will not create contraints and key in the destination table.

Steps
-Rightclick ur Database-Tasks-Export data or Import Data-Choose source Datasource-Database-

Choose Destination-database table-Next-Choose Data from table and view-Select source table and view

-Next-finish.You can select multiple table ,single table or all tables to be copied from one database to another.

Note :

1- Select into TableName will create a new table with same table structure and data but will not copy keys & constraints.

2-Insert into with Select * will work with both Tempory table and Table variable but select * into will not work with table variable.

Interactive Sorting in SSRS Reports

Interactive Sorting  in SSRS Reports

Many times you have requirement like clicking on column names or legend name will sort the complete column in either ascending order or descending order or if its is a value field then sort them alphabetically

This is what we call as Interactive sorting in SSRS Report.

Steps to Perform Interactive sorting in Your Report

Assuming that i have a report with Column Name EmpName displaying all columns Name

Select your EmpName Column -Right click on text box

-Text Box properties

-Interactive Sorting tab

-Check box-Enable Interactive Sorting on this textbox

Checkbox-Detail Row

sort by-EmployeeName or clicking Fx-Field!EmployeeName.value

Apply this Sortingto-"YourdatasetName"

Below ScreenShot showing all Properties.

Click ok 

Preview the Report 

Clicking on EmployeeName column Header will Sort the report alphabatically.

The Tablix is invalid Dataset property is missing or Table without Dataset in SSRS Report

The Tablix is invalid Dataset property is missing or Table without Dataset in SSRS Report

Today while creating a report i have requirement where i have to show some hardcoded values below the reports instead of writing separetely in each textboxes, i prefered table i dropped a table below my report data and copied all hardcoded data in the rows and columns as specified, when i preiview the report.

Report was throwing error.

The tablix 'Tablix2' is invalid. The value for the DataSetName property is missing

The reason was a table or tablix is always associated with a dataset but i was not using any dataset values i was directly hardcoding the values in the table rows and column.

So,Below is the Fix

Select  your newely created table 

Properties window-
General-Datasetname -which is blank change it to your report dataset Name.

or
Right click table-Tablix Properties-Select your Report datasetName.

Preview the Report the report will work without any issue.

Note:You can achieve the same thing by dropping textboxes in your report and copy hardcoded values in the textboxes but i believe it's quite time consuming as you have to drop multiple textboxes in the report then you have to aligned them properly.