CODEWITHSUNDEEP
sql-serversql-server-2005ssms
Suketa
Suketa

Reputation: 67

SQL Server : how to do mathematical computation for the values in one column

I have written this query:

SELECT 
    'APRIL _DATA_PASSED Only' AS 'ONE MONTH Audit_name',
   SUM(count2) AS Audit_count 
FROM
    (SELECT
         vCCSF.ClientName, 
         s.Name as SupplierName, 
         f.name as firstname, 
         vCCSF.CategoryName,
         r.Passed as passed,
         MONTH(CONVERT(DATE, r.CreateDate)) AS month, 
         YEAR(CONVERT(DATE, r.CreateDate)) AS year,
         source.name, 
         COUNT(*) AS count2
     FROM
         notr n
     JOIN
         NotrResult nr ON n.id = nr.NotrId
     JOIN
         results r ON r.NotrResultId = nr.id
     JOIN
         ProductLineFacilityMap plfm ON n.ProductLineFacilityMapId = plfm.id
     JOIN
         Facility f ON plfm.FacilityId = f.ID
     JOIN
         vClientCategorySubcategoryFilters vCCSF ON vCCSF.ProductLineId = plfm.ProductLineId
     JOIN
         Parameter p ON r.ParameterId = p.Id
     JOIN
         Supplier s ON f.SupplierId = s.id
     JOIN
         source ON p.SourceId = source.id
     WHERE
         n.Active = 1  
         AND n.Completed = 1  
         AND r.Active = 1 
         AND r.Passed = 1
         AND r.CreateDate BETWEEN '2018-04-01' AND '2018-04-30'
     GROUP BY
         vCCSF.ClientName, s.Name, f.name, 
         vCCSF.CategoryName, r.Passed, source.name, 
         MONTH(CONVERT(DATE, r.CreateDate)), 
         YEAR(CONVERT(DATE, r.CreateDate))
     HAVING
         vCCSF.ClientName LIKE 'Mc%' 
         AND s.name LIKE 'Ronpak%'   
         AND vCCSF.CategoryName LIKE 'Paper Bag%'  
         AND source.name = 'Conversion') AS tb

UNION ALL

SELECT 
    'APRIL _Conversion_DATA_BOTH' AS 'ONE MONTH Audit_name',
    SUM(count1) AS Audit_count
FROM
    (*same query as above, excluding this filter : r.Passed =1*) AS tb2

UNION ALL

SELECT 
    'APRIL _Conversion_DATA_BOTH' AS 'ONE MONTH Audit_name',
    SUM(count1) AS Audit_count
FROM
    (**same as above*
        AND r.Passed = 1
     GROUP BY
         *same as above*
     HAVING
         *same as above but changed the source filter*
         AND source.name = 'Raw Materials') AS tb3

UNION ALL

SELECT 
    'APRIL _Raw Materials_DATA_BOTH' AS 'ONE MONTH Audit_name',
    SUM(count1) AS Audit_count
FROM
    (*same query as above, excluding this filter : r.Passed = 1*
     AND source.name = 'Raw Materials') AS tb4

This output is returned:

|ONE MONTH Audit_name|      |Audit_count|  
|APRIL _Conversion_McDonalds_Ronpak_Paper Bags_PASSED Only|   |124|  
|APRIL _Conversion_McDonalds_Ronpak_Paper Bags_BOTH|          |150|  
|APRIL_Raw Materials_McDonalds_Ronpak_Paper Bags_PASSED Only| |16|  
|APRIL _Raw Materials_McDonalds_Ronpak_Paper Bags_BOTH|       |70|

Problem

Looking for solution where I could perform divisions

  1. first row / second row
  2. third row / forth row

UNION ALL is used intentionally to get all results in one page but it will be great if solution comes in this way

Conversion        Raw Material                 Overall
(PASSED only /    ( PASSED only / BOTH)        SUM(Conversion+ Raw Material   
                                                )passed /                                              
                                                  BOTH conv and raw

Any suggestions, please?

Upvotes: 0

Views: 83

Answers (2)

Suketa
Suketa

Reputation: 67

DECLARE @AprilConversionPass DECIMAL(11,2)
DECLARE @AprilConversionBOTH DECIMAL(11,2)
DECLARE @AprilRawMaterialPass DECIMAL(11,2)
DECLARE @AprilRawMaterialBOTH DECIMAL(11,2)

SET @AprilConversionPass = (
SELECT 
    'APRIL _Conversion_DATA_BOTH' AS 'ONE MONTH Audit_name',
    SUM(count1) AS Audit_count
FROM
    (*same query as above, excluding this filter : r.Passed =1*) AS tb2
);
SET @AprilConversionBOTH () ; and so on


SELECT '1. _Conversion_McDonalds_Ronpak_Paper Bags_PASSED Only' AS 'SIX MONTH Audit_name',
 @AprilConversionPass AS Audit_count
union all
SELECT '2. _Conversion_McDonalds_Ronpak_Paper Bags_BOTH' AS 'SIX MONTH Audit_name',
 @AprilConversionBOTH AS Audit_count and so on

Upvotes: 0

NP007
NP007

Reputation: 688

Just create 4 variable for each calculation. And store results in each variable based on your query. And after that you can use that variables in calculation.

Just give you one example as below.

DECLARE @AprilConversionPass DECIMAL(11,2)

SET @AprilConversionPass = (
SELECT 
    'APRIL _Conversion_DATA_BOTH' AS 'ONE MONTH Audit_name',
    SUM(count1) AS Audit_count
FROM
    (*same query as above, excluding this filter : r.Passed =1*) AS tb2
)

In this way you can set up your all 4 variables and then use in your select query as per mathematical operation.

Upvotes: 3

Related Questions