clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 921282 fiddles created (11979 in the last week).

SET DATEFORMAT dmy CREATE TABLE ILE ( Posting_Date DATE NOT NULL ,Item_No_ VARCHAR(20) NOT NULL ,Document_No_ VARCHAR(20) NOT NULL PRIMARY KEY ,Location_Code VARCHAR(10) NOT NULL ,Quantity INT NOT NULL ,Item_Category_Code VARCHAR(10) NOT NULL ); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('03-07-2017','FG-019325','PAO/17-18/03971','NOD',4,'CYLINDERS'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('03-07-2017','FG-030222','PAO/17-18/03977','NOD',44,'S&P VA'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('26-06-2017','FG-033154','PAO/17-18/03790','NOD',20,'CYLINDERS'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('26-06-2017','FG-027182','PAO/17-18/03797','NOD',50,'S&P VA'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('30-06-2017','FG-000242','PAO/17-18/03941','NOD',10,'CYLINDERS'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('03-07-2017','FG-026591','PAO/17-18/03979','NOD',1,'S&P VA'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('03-07-2017','FG-027302','PAO/17-18/03955','NOD',1,'CYLINDERS'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('03-07-2017','FG-036994','PAO/17-18/03970','NOD',1,'CYLINDERS'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('03-07-2017','FG-037249','PAO/17-18/03974','NOD',2,'CYLINDERS'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('26-06-2017','FG-037133','PAO/17-18/03813','NOD',10,'CYLINDERS'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('26-06-2017','FG-025815','PAO/17-18/03823','NOD',10,'S&P VA'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('29-06-2017','FG-047645','PAO/17-18/03923','NOD',10,'CYLINDERS'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('03-07-2017','FG-018959','PAO/17-18/03972','NOD',6,'CYLINDERS'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('03-07-2017','FG-030435','PAO/17-18/03969','NOD',1,'CYLINDERS'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('03-07-2017','FG-047599','PAO/17-18/03973','NOD',2,'CYLINDERS'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('26-06-2017','FG-044910','PAO/17-18/03818','NOD',10,'S&P VA'); INSERT INTO ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('27-06-2017','FG-000366','PAO/17-18/03873','NOD',10,'CYLINDERS');
17 rows affected
 hidden batch(es)


select YEAR(Posting_Date) as Year_Posting ,MONTH(Posting_Date) as Month_Posting --,SUM(CASE WHEN Item_Category_Code ='Cylinders' THEN Quantity ELSE 0 END) as Cylinders --,SUM(CASE WHEN Item_Category_Code ='S&P VA' THEN Quantity ELSE 0 END) as [S&P VA] ,CAST( SUM(CASE WHEN Item_Category_Code ='Cylinders' THEN Quantity ELSE 0 END)*1.0 /COUNT(Posting_Date) AS DECIMAL(8,2)) as Cylinders ,CAST( SUM(CASE WHEN Item_Category_Code ='S&P VA' THEN Quantity ELSE 0 END)*1.0/COUNT(Posting_Date) AS DECIMAL(8,2)) as [S&P VA] --,SUM(CASE WHEN Item_Category_Code ='Cylinders' THEN Quantity ELSE 0 END)*1.0 --,SUM(CASE WHEN Item_Category_Code ='S&P VA' THEN Quantity ELSE 0 END)*1.0 from ILE AS I group by YEAR(Posting_Date) , MONTH(Posting_Date)
Year_Posting Month_Posting Cylinders S&P VA
2017 6 7.50 8.75
2017 7 1.89 5.00
 hidden batch(es)