Click here to Skip to main content
15,884,388 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Table :
SQL
CREATE TABLE PUBLISHER_BATCH
(
  PUBLISHER_ID INT,
  NAME_PUBLISHER VARCHAR(50),
  NO_RECORDS_IMPORTED INT,
  NO_RECORDS_EXPORTED INT,
  NO_RECORDS_DELETED INT,
  STATUS INT,
  LOCKED INT,
  SOURCE INT
)


INSERT INTO PUBLISHER_BATCH VALUES(1,'Publisher1',18,10,3,' 2',1,2)
INSERT INTO PUBLISHER_BATCH VALUES(2,'Publisher2',28,12,5,' 2',1,2)
INSERT INTO PUBLISHER_BATCH VALUES(3,'Publisher3',25,0,0,' 1',0,1)

but my requirment to display result is like below :

PUBLISHER_NAME  OnStock   Imported   Exported   Deleted  Total
Publisher1         0         18         10          3       31
Publisher2         0         27         11          5       43
Publisher3        25          0          0          0       25
Total             25         45         21          8       99


OnStock : Count of all the records where LOCKED=0 and Status=1

I am not able to make the "OnStock" value from the sql query. I am very new in the system..
Can anyone please help me to generate this Result set in Sql server 2008 and
and how i will get the total in both rows (extremely right row and extremely below row)

Thanks in Advance.
Posted
Updated 9-May-14 6:24am
v2

Try this:
SQL
DECLARE @PUBLISHER_BATCH TABLE 
(  PUBLISHER_ID INT,   NAME_PUBLISHER VARCHAR(50),   NO_RECORDS_IMPORTED INT,
  NO_RECORDS_EXPORTED INT,   NO_RECORDS_DELETED INT,   STATUS INT,   LOCKED INT,
  SOURCE INT )
 
INSERT INTO @PUBLISHER_BATCH VALUES(1,'Publisher1',18,10,3,' 2',1,2)
INSERT INTO @PUBLISHER_BATCH VALUES(2,'Publisher2',27,11,5,' 2',1,2)
INSERT INTO @PUBLISHER_BATCH VALUES(3,'Publisher3',25,0,0,' 1',0,1)
 
SELECT NAME_PUBLISHER, OnStock, NO_RECORDS_IMPORTED, NO_RECORDS_EXPORTED, NO_RECORDS_DELETED, NO_RECORDS_IMPORTED + NO_RECORDS_EXPORTED + NO_RECORDS_DELETED AS TOTAL
FROM (
	SELECT T1.NAME_PUBLISHER, COALESCE(T2.OnStock,0) AS OnStock, T1.NO_RECORDS_IMPORTED, T1.NO_RECORDS_EXPORTED, T1.NO_RECORDS_DELETED
	FROM @PUBLISHER_BATCH AS T1 
	LEFT JOIN (
		SELECT NAME_PUBLISHER, COUNT(*) AS OnStock
		FROM @PUBLISHER_BATCH
		WHERE LOCKED =0 AND STATUS =1
		GROUP BY NAME_PUBLISHER 
		) AS T2 ON T1.NAME_PUBLISHER = T2.NAME_PUBLISHER
	UNION ALL
	SELECT 'Total' AS NAME_PUBLISHER, (SELECT COUNT(*) FROM @PUBLISHER_BATCH WHERE LOCKED = 0 AND STATUS =1) AS OnStock, SUM(NO_RECORDS_IMPORTED) AS NO_RECORDS_IMPORTED, SUM(NO_RECORDS_EXPORTED) AS NO_RECORDS_EXPORTED,
			SUM(NO_RECORDS_DELETED) AS NO_RECORDS_DELETED 
	FROM @PUBLISHER_BATCH  
) AS T


Result:
Name_Pub..	OnS	Imp	Exp	Del	Total
Publisher1	0	18	10	3	31
Publisher2	0	27	11	5	43
Publisher3	1	25	0	0	25
Total		1	70	21	8	99
 
Share this answer
 
v6
Comments
bubai banerjee 12-May-14 0:48am    
Hi Sir,<br>
Thank you very much for your kind reply. but i want "Onstock" field mostly... so please look into this again which can help me a lot from your reply.. "Count of all the records where LOCKED=0 and Status=1" is the condition for onstock field.please assist me once more, to display it in the report which i had shown in the above, in tabular format.Thanks in advance.
Maciej Los 12-May-14 2:37am    
See updated solution ;)
bubai banerjee 12-May-14 4:19am    
Thank you so much sir. It is really as per my expectation and working very fine.Thanks for your time and kind support.
once again, a truly thank you from my heart.
Maciej Los 12-May-14 4:56am    
You're welcome ;)
Call again ;)
bubai banerjee 12-May-14 5:29am    
Hello Sir, Sorry, to distrub you again. there is a little problem i am facing now.<br>
i want the total of @OnStock, which should be displayed in below and it's summation will display in result.<br>
 <br>
in our case, the onstock is not displayed beside the "Total" Column and it is not adding with lower most "Total".<br>
Please extend your hand once again, so i will able to generate the actual report. i am very new in this field. so, please help me.<br>
In my above table(what i displayed), the @OnStock is displaying after the 'Total' column , and main total comeing same in horizontally and vertically .<br>
so, my request is please help me once again..
See my article about pivoting: here[^]
 
Share this answer
 

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