Click here to Skip to main content
14,766,265 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 24-Aug-20 0:07am

You have some problems with your sample data - when I tried to use it (Note I'm using temporary versions of your tables) I got an error message
Quote:
An explicit value for the identity column in table '#TBL_DISTRIBUTOR' can only be specified when a column list is used and IDENTITY_INSERT is ON.
However, the road goes ever onward...
Quote:
I need to show data about total distribution on quarterly basis. I mean how many total productions are distributed in each quarter.
When I read that I automatically "see" in my head the data presented like this
PRODUCTION_ID	PRODUCTION_NAME	Q1	Q2	Q3	Q4
1		SGU		25	25	35	0
2		BGU		25	25	50	0
So for me that would involve using a PIVOT table - have a look at Simple Way To Use Pivot In SQL Query[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900