I have two tables
CREATE TABLE [DBO].[TBL_PRODUCTION] (
PRODUCTION_ID INT IDENTITY(1, 1) NOT NULL
,PRODUCTION_NAME NVARCHAR(200) NOT NULL
,PRODUCTION_TYPE INT NOT NULL
,PRODUCTION_QUANTITY INT
,CONSTRAINT PK_PRODUCTION PRIMARY KEY (PRODUCTION_ID)
)
INSERT INTO [DBO].[TBL_PRODUCTION] VALUES ('SGU',1, 100)
INSERT INTO [DBO].[TBL_PRODUCTION] VALUES ('BGU',2, 150)
CREATE TABLE [DBO].[TBL_DISTRIBUTOR] (
DISTRIBUTOR_ID INT IDENTITY(1, 1) NOT NULL
,PRODUCTION_ID INT NOT NULL
,QUARTER_TYPE INT NOT NULL
,DEMAND_QUANTITY INT
,CONSTRAINT PK_DISTRIBUTOR PRIMARY KEY (DISTRIBUTOR_ID)
)
INSERT INTO [DBO].[TBL_DISTRIBUTOR] VALUES (1,555,1,25)
INSERT INTO [DBO].[TBL_DISTRIBUTOR] VALUES (1,555,2,25)
INSERT INTO [DBO].[TBL_DISTRIBUTOR] VALUES (1,655,3,25)
INSERT INTO [DBO].[TBL_DISTRIBUTOR] VALUES (2,555,1,25)
INSERT INTO [DBO].[TBL_DISTRIBUTOR] VALUES (2,745,2,25)
INSERT INTO [DBO].[TBL_DISTRIBUTOR] VALUES (3,745,3,25)
INSERT INTO [DBO].[TBL_DISTRIBUTOR] VALUES (1,745,3,10)
INSERT INTO [DBO].[TBL_DISTRIBUTOR] VALUES (2,745,3,50)
I need to show data about total distribution on quarterly basis. I mean how many total productions are distributed in each quarter.
The query need to be GROUP BY only PRODUCTION_ID not others but when I do so, I am getting error as
Column 'DBO.TBL_PRODUCTION.PRODUCTION_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
And when I comment A.QUARTER_TYPE in GROUP BY Clause I am getting error as::
Column 'DBO.TBL_DISTRIBUTOR.QUARTER_TYPE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Why is it so? Or is there any problem with my query?
What I have tried:
SELECT
A.PRODUCTION_ID
,B.PRODUCTION_NAME
,A.QUARTER_TYPE
,SUM(A.DEMAND_QUANTITY) [TOTAL DISTRIBUTED]
FROM [DBO].[TBL_DISTRIBUTOR] A
INNER JOIN [DBO].[TBL_PRODUCTION] B ON A.PRODUCTION_ID = B.PRODUCTION_ID
GROUP BY
A.PRODUCTION_ID
,B.PRODUCTION_NAME
,A.QUARTER_TYPE
ORDER BY A.PRODUCTION_ID