First Think of About Group by Query in SQL

Introduction

In the past week my TL give me some work. In the work I have three tables and every table is linked with a primary key and foreign key.

table

Ay first look this table looks very simple but if we add one more condition then that can make this query a bit messy. The preceding records get more than three tables. The table details are shown below.

select CustomerID,CompanyName from tbl_Customer

CustomerID CompanyName
1 Love Lights
4 Edmundson Electrical (Guernsey)
5 PJH Group Ltd
6 O Neills Kitchen Supplies Ltd
7 Edmundson Electrical (kingslynn)
8 Dream Home T/AS Urban Myth
9 Onefit Ltd
10 Mereway Kitchens Ltd
11 Edmunson Electrical (Tonbridge)
12 Express Indian Cuisine **

select Fk_customerID,Login_Time from tbl_loginHistory

Fk_customerID Login_Time
30365 2014-12-16 12:07:56.160
30365 2014-12-16 12:13:07.727
30365 2014-12-16 15:23:55.143
30365 2014-12-16 16:58:00.090
30365 2014-12-16 17:08:26.430
30365 2014-12-16 17:10:37.100
30365 2014-12-16 17:11:17.453
30365 2014-12-16 17:24:20.257
30365 2014-12-16 17:58:22.473

select CustomerID,OrderDate from tbl_Orderheader

CustomerID Order Date
30365 2014-12-16 12:07:56.160
30365 2014-12-16 12:13:07.727
30365 2014-12-16 1:13:07.727
30365 2014-12-16 2:13:07.727
30365 2014-12-16 12:1:07.727
30365 2014-12-16 12:3:07.727
30365 2014-12-16 12:30:07.727
30365 2014-12-16 12:34:07.727
30365 2014-12-16 12:20:07.727
30365 2014-12-16 12:17:07.727

Try One:

select C.CompanyName,COUNT(lh.Fk_customerID) from tbl_LoginHistory LH
inner join tbl_Customer C on LH.Fk_customerID=c.CustomerID
inner join tbl_OrderHeader OH on LH.Fk_customerID=oh.CustomerID
where MONTH(LH.login_time)= 12 and year(LH.login_time)=2014 group by LH.Fk_customerID

If we run this query then compiler throws the error:

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

Note: The receding query can't be solved because we need a count of the until date.

Finally, I found one simple and best way to solve this type of problem.

ALTER proc [dbo].[Proc_CustomerLoginHistory] 2014,12
(
@years varchar(max),
@month varchar(max)
)
as
begin
;with customerlist(csid,companyname)
as
(
select distinct CustomerID ,CompanyName from tbl_Customer
),
monthtotallogin(csid,monthlycount)
as
(
select fk_customerid as csid,count(*) monthlycount from tbl_LoginHistory
where MONTH(login_time)= @month and year(login_time)=@years group by Fk_customerID having Fk_customerID>0
),
totalcount(csid,totallogincount)
as
(
select fk_customerid as csid,count(*) as totallogincount from tbl_LoginHistory group by Fk_customerID having Fk_customerID>0
),
Totalorderofmonth(csid,monthlyordercount)
as
(
select CustomerID as csid,COUNT(*) as monthlyordercount from tbl_OrderHeader
where MONTH(OrderDate)= @month and year(OrderDate)=@years
group by CustomerID having CustomerID>0
),
Totalordero(csid,ordercount)
as
(
select CustomerID as csid,COUNT(*) as monthlyordercount from tbl_OrderHeader group by CustomerID having CustomerID>0
)
select CS.companyname as [Company Name],isnull(ML.monthlycount,0) as [This Month Total Login],
isnull(TL.totallogincount,0) as [Total Login Till Date],isnull(TM.monthlyordercount,0) as [Total Order this Month], isnull(TOR.ordercount,0)as [Total Order Till Date]
from customerlist cs
left join monthtotallogin ML on cs.csid=ML.csid
left join totalcount TL on cs.csid=TL.csid
left join Totalorderofmonth TM on cs.csid=TM.csid
left join Totalordero TOR on cs.csid=TOR.csid
where monthlycount>0
end

Output of above query

Company Name This Month Total Login Total Login Till Date Total Order this Month Total Order Till Date
Website Testing 37 37 1 1

Final word

If you have a question or information about this problem then drop your comments below in the comment box.

3 Comments

Join the conversation! Your thoughts help the community grow.

pertti jarva11y ago
You should never ever use functions in SARGs, like "MONTH(OrderDate)= @month and year(OrderDate)=@years" this is really bad coding and SQL Server has no way to use indexes for this, and normally also cardinality estimation is totally off. Instead you should do something like OrderDate >= @startTime and OrderDate < @endTime
Jitendra Kumar12y ago
Nice..
Manish Kumar Choudhary12y ago
good one..