Unfiltered data in a group by statement – SQL Server

I have created a SQL statement using group by and some filters but I received an unexpected result:

ID | Product | Brand | years    | Sales
---+---------+-------+----------+----------
1  | car     | 1     | 20170331 | 5123.00
2  | car     | 1     | 20180331 | 2556.00
3  | car     | 2     | 20170331 | 2080.00

As we can see in the field brand we have code 1 and code 2 but in my query I already made a filter to get just 1:

declare
@CurrentDate varchar(8),
@LastDate    varchar(8)

set @CurrentDate = '20180331';
set @LastDate = convert(NVARCHAR(255),convert(INT,LEFT(@CurrentDate,6))-100);

select b.Product,b.Brand,b.years,sum(b.Sales) Sales from myTable b
where 
b.Brand='1' and
b.years = @CurrentDate or b.years like concat(@LastDate,'%') 
group by b.Product,b.Brand,b.years order by b.Product

I don’t know why I have this result. My expected result is this:

ID | Product | Brand | years    | Sales
---+---------+-------+----------+----------
1  | car     | 1     | 20170331 | 5123.00
2  | car     | 1     | 20180331 | 2556.00

If I use having I resolve the problem in this example but in my real life problem I must not use having.

Here is the script of my table:

CREATE TABLE myTable 
(
    Id int IDENTITY(1,1) PRIMARY KEY,
    Product varchar(255),
    Brand varchar(255),
    years varchar(255),
    Sales decimal(18,2),
);


INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','1','20170331','543.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','1','20180331','380.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','1','20180331','821.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','1','20180331','635.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','1','20180331','720.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','1','20170331','543.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','1','20170331','55.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','1','20170331','874.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','1','20170331','552.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','1','20170331','380.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','1','20170331','821.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','1','20170331','635.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','1','20170331','720.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','2','20170331','900.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','2','20170331','70.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','2','20170331','100.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','2','20170331','500.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','2','20170331','300.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','2','20170331','80.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','2','20170331','60.00');
    
INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('car','2','20170331','70.00');

Answer

you are missing parentheses in the query

SELECT
        b.Product
        , b.Brand
        , b.years
        , SUM(b.Sales) Sales
    FROM
        myTable b
    WHERE
        b.Brand = '1'
        AND (
           b.years = '20180331'
           OR b.years = '20170331'
           )
    GROUP BY
        b.Product
        , b.Brand
        , b.years
    ORDER BY
        b.Product;

however here is a better way to do it

SELECT
    b.Product
    , b.Brand
    , b.years
    , SUM(b.Sales) Sales
FROM
    myTable b
WHERE
    b.Brand = '1'
    AND b.years IN ('20180331', '20170331')
GROUP BY
    b.Product
    , b.Brand
    , b.years
ORDER BY
    b.Product;

Advertisement

Answer