SQL group by clause issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL group by clause issue

Author

Topic

neellotus
Starting Member

15 Posts

Posted - 2016-09-04 : 08:51:20

Hi all,

kindly find the below detail of my table:-

1. Query to CREATE TABLE

CREATE TABLE TBL_001

(
DOCKET_NO VARCHAR(9),
DATE DATETIME,
PARTY_CD VARCHAR(10),
ITEM_CD VARCHAR(15),
ITEM_DIMENSION VARCHAR(100),
LOT_NOS VARCHAR(15),
QTY1 DECIMAL(14,6),
QTY2 DECIMAL(14,6)
)

2. Query to INSERT DATA

INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2)
VALUES('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','HEIGHT','AA8/ZZ16',2.25)

INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2)
VALUES('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','INNER DIA','AA8/ZZ16',40.1)

INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2)
VALUES('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','INNER DIA 1','AA8/ZZ16',37.8)

INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2)
VALUES('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','PIERCING DIA','AA8/ZZ16',33.4)

INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2)
VALUES('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','THICKNESS','AA8/ZZ16',0.88)

INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2)
VALUES('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','THICKNESS 1','AA8/ZZ16',0.71)

INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2)
VALUES('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','TOTAL HEIGHT','AA8/ZZ16',9)

3. Query to Display table data

SELECT DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2 FROM TBL_001

4. my query to get desire data :-

SELECT DATE,PARTY_CD,ITEM_CD,LOT_NOS,INNER_DIA,THK FROM
(SELECT DATE,PARTY_CD,ITEM_CD,LOT_NOS,QTY2 INNER_DIA,0 THK FROM TBL_001
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DATE,PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY2 THK FROM TBL_001
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')) Z WHERE LOT_NOS='AA8/ZZ16'
GROUP BY DATE,PARTY_CD,ITEM_CD,LOT_NOS,INNER_DIA,THK

Result of above query :-

DATE PARTY_CD ITEM_CD LOT_NOS INNER_DIA THK
20-08-2016 PARTY0002 1285BRF21OP AA8/ZZ16 0 0.71
20-08-2016 PARTY0002 1285BRF21OP AA8/ZZ16 0 0.88
20-08-2016 PARTY0002 1285BRF21OP AA8/ZZ16 37.8 0
20-08-2016 PARTY0002 1285BRF21OP AA8/ZZ16 40.1 0

but i m trying to get below result :-
DATE PARTY_CD ITEM_CD LOT_NOS INNER_DIA THK
20-08-2016 PARTY0002 1285BRF21OP AA8/ZZ16 37.8 0.71
20-08-2016 PARTY0002 1285BRF21OP AA8/ZZ16 40.1 0.88

i also tried inner join and left join but not getting desire result so please help me i will be very thankfull to you...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources