First Think of About Group by Query in SQL

Joginder Banger
10y
1.6k
0
1

Blog

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