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.

  1. select CustomerID,CompanyName from tbl_Customer   
  1. CustomerID              CompanyName    
  2.     1               Love Lights    
  3.     4               Edmundson Electrical (Guernsey)    
  4.     5               PJH Group Ltd    
  5.     6               O Neills Kitchen Supplies Ltd    
  6.     7               Edmundson Electrical (kingslynn)    
  7.     8               Dream Home T/AS Urban Myth    
  8.     9               Onefit Ltd    
  9.     10              Mereway Kitchens Ltd    
  10.     11              Edmunson Electrical (Tonbridge)    
  11.     12              Express Indian Cuisine **    
  1. select Fk_customerID,Login_Time from tbl_loginHistory   
  1. Fk_customerID                   Login_Time    
  2.    30365                  2014-12-16 12:07:56.160    
  3.    30365                  2014-12-16 12:13:07.727    
  4.    30365                  2014-12-16 15:23:55.143    
  5.    30365                  2014-12-16 16:58:00.090    
  6.    30365                  2014-12-16 17:08:26.430    
  7.    30365                  2014-12-16 17:10:37.100    
  8.    30365                  2014-12-16 17:11:17.453    
  9.    30365                  2014-12-16 17:24:20.257    
  10.    30365                  2014-12-16 17:58:22.473    
  1. select CustomerID,OrderDate from tbl_Orderheader   
  1. CustomerID        Order            Date      
  2.  30365         2014-12-16       12:07:56.160    
  3.  30365         2014-12-16       12:13:07.727     
  4.  30365         2014-12-16       1:13:07.727      
  5.  30365         2014-12-16       2:13:07.727      
  6.  30365         2014-12-16       12:1:07.727      
  7.  30365         2014-12-16       12:3:07.727      
  8.  30365         2014-12-16       12:30:07.727      
  9.  30365         2014-12-16       12:34:07.727      
  10.  30365         2014-12-16       12:20:07.727      
  11.  30365         2014-12-16       12:17:07.727     
Try One:
  1. select C.CompanyName,COUNT(lh.Fk_customerID) from tbl_LoginHistory LH     
  2. inner join tbl_Customer C on  LH.Fk_customerID=c.CustomerID      
  3. inner join tbl_OrderHeader OH on LH.Fk_customerID=oh.CustomerID      
  4. 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.

 

  1. ALTER proc [dbo].[Proc_CustomerLoginHistory] 2014,12    
  2. (    
  3.    @years varchar(max),    
  4.    @month varchar(max)    
  5. )    
  6. as    
  7. begin    
  8. ;with customerlist(csid,companyname)    
  9. as    
  10. (    
  11.     select distinct  CustomerID ,CompanyName from tbl_Customer     
  12.   
  13. ),    
  14. monthtotallogin(csid,monthlycount)    
  15. as    
  16. (    
  17.    select fk_customerid as csid,count(*) monthlycount from tbl_LoginHistory    
  18.    where MONTH(login_time)= @month and year(login_time)=@years group by Fk_customerID  having Fk_customerID>0    
  19. ),    
  20. totalcount(csid,totallogincount)    
  21. as    
  22. (    
  23.    select fk_customerid as csid,count(*) as  totallogincount from tbl_LoginHistory group by Fk_customerID having Fk_customerID>0    
  24. ),    
  25. Totalorderofmonth(csid,monthlyordercount)    
  26. as    
  27. (    
  28.    select CustomerID as csid,COUNT(*) as monthlyordercount  from tbl_OrderHeader    
  29.    where MONTH(OrderDate)= @month and year(OrderDate)=@years   
  30.    group by CustomerID  having CustomerID>0    
  31. ),    
  32. Totalordero(csid,ordercount)    
  33. as    
  34. (    
  35.    select CustomerID as csid,COUNT(*) as monthlyordercount  from tbl_OrderHeader group by CustomerID  having CustomerID>0    
  36. )    
  37.     
  38.     
  39.    select CS.companyname as [Company Name],isnull(ML.monthlycount,0) as [This Month Total Login],  
  40.    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]  
  41.    from customerlist cs    
  42.    left join monthtotallogin ML on cs.csid=ML.csid    
  43.    left join totalcount TL on cs.csid=TL.csid    
  44.    left join Totalorderofmonth TM on cs.csid=TM.csid    
  45.    left join Totalordero TOR on cs.csid=TOR.csid  
  46.    where monthlycount>0    
  47.     
  48. end    
Output of above query
  1. Company Name       This Month Total Login Total Login Till Date Total Order this Month Total Order Till Date  
  2.     
  3. 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.