|
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...
|
|
SQL group by clause issue