user2331670 user2331670 - 3 months ago 8
SQL Question

Sub select SQL query using where and groupby

When I try below code in SQL Server,

SELECT
dbo.Category.CatNo as Category,
dbo.Category.Categaory as Name,
(select SUM(dbo.SALES.SELLINC) where (dbo.SALES.DATE BETWEEN '2016-07-17' AND '2016-07-23')) AS ActualSales,
(select SUM(dbo.SALES.SELLINC) where (dbo.SALES.DATE BETWEEN '2015-07-19' AND '2015-07-25')) AS LastYrVariance,
(select SUM(dbo.SALES.SELLINC) where (dbo.SALES.DATE BETWEEN '2016-01-01' AND '2016-09-05')) AS YrToDateActual,
(select SUM(dbo.SALES.SELLINC) where (dbo.SALES.DATE BETWEEN '2015-01-01' AND '2015-09-05')) AS LastYrToDateActual
FROM dbo.Category INNER JOIN
dbo.Dissection ON dbo.Category.CatNo = dbo.Dissection.CatNo INNER JOIN
dbo.Division ON dbo.Dissection.DivNo = dbo.Division.ID INNER JOIN
dbo.Departments ON dbo.Dissection.DeptNo = dbo.Departments.DeptID INNER JOIN
dbo.SALES ON dbo.Dissection.DissNo = dbo.SALES.CODE
WHERE (dbo.SALES.BRN = 1)

GROUP BY dbo.Category.CatNo, dbo.Category.Categaory
ORDER BY dbo.Category.CatNo


I get below error message,

Msg 8120, Level 16, State 1, Line 2
Column 'dbo.Category.CatNo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Any help please?

Answer

I cant find a reason to generate that error in your query. Between you can simply those sub-queries by using CASE statements

SELECT dbo.category.catno     AS Category, 
       dbo.category.categaory AS NAME, 
       Sum(CASE WHEN dbo.sales.date BETWEEN '2016-07-17' AND '2016-07-23' THEN dbo.sales.sellinc END) AS ActualSales, 
       Sum(CASE WHEN dbo.sales.date BETWEEN '2015-07-19' AND '2015-07-25' THEN dbo.sales.sellinc END) AS LastYrVariance, 
       Sum(CASE WHEN dbo.sales.date BETWEEN '2016-01-01' AND '2016-09-05' THEN dbo.sales.sellinc END) AS YrToDateActual, 
       Sum(CASE WHEN dbo.sales.date BETWEEN '2015-01-01' AND '2015-09-05' THEN dbo.sales.sellinc END) AS LastYrToDateActual 
FROM   dbo.category 
       INNER JOIN dbo.dissection 
               ON dbo.category.catno = dbo.dissection.catno 
       INNER JOIN dbo.division 
               ON dbo.dissection.divno = dbo.division.id 
       INNER JOIN dbo.departments 
               ON dbo.dissection.deptno = dbo.departments.deptid 
       INNER JOIN dbo.sales 
               ON dbo.dissection.dissno = dbo.sales.code 
WHERE  ( dbo.sales.brn = 1 ) 
GROUP  BY dbo.category.catno, 
          dbo.category.categaory 
ORDER  BY dbo.category.catno 
Comments