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.



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.