Creating Sumif Function in SQL Server 2012

How to write a SQL query implementing Excel SUMIFS function

Your query is ok, but it can be improved a bit:

SELEC Date,
      MAX(CASE WHEN Department = 'Toys' THEN Sales else 0 END) as [Toys],
      MAX(CASE WHEN Department = 'Movies' THEN Sales else 0 END) as [Movies]
FROM Table$
WHERE store in ('A', 'B')
GROUP BY Date
ORDER BY Date;

This removes the distinct, which is unnecessary with a group by. It moves the condition on store to the where clause, because it applies to all the rows. And, it removes the ISNULL() by including else 0 in the case statement -- so stores with no sales in the department will return a 0 instead of NULL.

SUMIFS (Sum if with multiple conditions) with SQL Server

Since you are using SQL Server, if you are using SQL Server 2005+ then you can use the PIVOT function to get the result. This solution implements both an unpivot and a pivot process to get the result. The starting point for this result is to calculate the total percent and total by type:

select type, year, total,
  round(total / sum(total) over(partition by year)*100.0, 1) t_per,
  sum(total) over(partition by type) t_type,
  round(sum(total) over(partition by type)*100.0/sum(total) over(), 1) tot_per      
from tablea 

See SQL Fiddle with Demo. This will give a result with multiple columns that you want to pivot so you can unpivot the data into multiple rows using CROSS APPLY:

select type, 
  col = cast(year as varchar(4))+'_'+col,
  value,
  t_type
from 
(
  select type, year, total,
    round(total / sum(total) over(partition by year)*100.0, 1) t_per,
    sum(total) over(partition by type) t_type,
    round(sum(total) over(partition by type)*100.0/sum(total) over(), 1) tot_per   
  from tablea
) d
cross apply
(
  select 'total', total union all
  select 't_per', t_per 
) c (col, value);

See Demo. Finally you can apply the PIVOT function to the values in col:

select type, 
  [2010_total], [2010_t_per],
  [2011_total], [2011_t_per],
  [2012_total], [2012_t_per],
  t_type,
  tot_per
from
(
  select type, 
    col = cast(year as varchar(4))+'_'+col,
    value,
    t_type,
    tot_per
  from 
  (
    select type, year, total,
      round(total / sum(total) over(partition by year)*100.0, 1) t_per,
      sum(total) over(partition by type) t_type,
      round(sum(total) over(partition by type)*100.0/sum(total) over(), 1) tot_per      
    from tablea 
  ) d
  cross apply
  (
    select 'total', total union all
    select 't_per', t_per 
  ) c (col, value)
) s
pivot
(
  max(value)
  for col in ([2010_total], [2010_t_per],
              [2011_total], [2011_t_per],
              [2012_total], [2012_t_per])
) piv

See SQL Fiddle with Demo. This could be refactored to use a CTE instead of the subqueries and this could also be converted to use dynamic SQL if the year will be unknown.

If you have an unknown number of values, then the dynamic SQL code will be:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(cast(year as varchar(4))+'_'+col) 
                    from tablea
                    cross apply
                    (
                      select 'total', 1 union all
                      select 't_per', 2 
                    ) c (col, so)
                    group by year, col, so
                    order by year, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT type,' + @cols + ', t_type, tot_per
            from 
            (
              select type, 
                col = cast(year as varchar(4))+''_''+col,
                value,
                t_type,
                tot_per
              from 
              (
                select type, year, total,
                  round(total / sum(total) over(partition by year)*100.0, 1) t_per,
                  sum(total) over(partition by type) t_type,
                  round(sum(total) over(partition by type)*100.0/sum(total) over(), 1) tot_per      
                from tablea 
              ) d
              cross apply
              (
                select ''total'', total union all
                select ''t_per'', t_per 
              ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See Demo. Both the static version and the dynamic version give the result:

| TYPE | 2010_TOTAL | 2010_T_PER | 2011_TOTAL | 2011_T_PER | 2012_TOTAL | 2012_T_PER | T_TYPE | TOT_PER |
---------------------------------------------------------------------------------------------------------
|    A |          1 |       16.7 |          1 |       16.7 |          1 |       16.7 |      3 |    16.7 |
|    B |          2 |       33.3 |          2 |       33.3 |          2 |       33.3 |      6 |    33.3 |
|    C |          3 |         50 |          3 |         50 |          3 |         50 |      9 |      50 |

Using Sumif in SQL Server2008

SUM(IIF()) is the equivalent of using an aggregate with a CASE expression:

sum(case 
        when [entry_date] >= '2012-12-01' and [entry_date] <= '2012-12-31' 
        then [sumofentry_amount] 
        else 0 
    end) AS [Dec'12]

Then you will just repeat this as needed for your other columns.

The CASE and the aggregate function are pivoting the data from the row values into columns. In SQL Server 2005+ you can implement the PIVOT function, but you can also use something similar to below:

select CLINIC_ID,
    SUMMARY_CATGRY,
    ENTRY_TYPE,
    SUM(case when EntryMonth = 12 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Dec12],
    SUM(case when EntryMonth = 11 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Nov12],
    SUM(case when EntryMonth = 10 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Oct12],
    SUM(case when EntryMonth = 9 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Sep12],
    SUM(case when EntryMonth = 8 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Aug12],
    SUM(case when EntryMonth = 7 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Jul12],
    SUM(case when EntryMonth = 6 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Jun12],
    SUM(case when EntryMonth = 5 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [May12],
    SUM(case when EntryMonth = 4 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Apr12],
    SUM(case when EntryMonth = 3 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Mar12],
    SUM(case when EntryMonth = 2 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Feb12],
    SUM(case when EntryMonth = 1 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Jan12],
    SUM(case when EntryYear = 2012 then [sumofentry_amount] else 0 end) [2012YTD],
    SUM(case when EntryYear = 2011 then [sumofentry_amount] else 0 end) [2011YTD]
from
(
    SELECT [Non Recurring Clinic Step1].CLINIC_ID, 
        dbo_HR_FINAN_CLASS.SUMMARY_CATGRY, 
        [TABLE LAYOUT].ENTRY_TYPE, 
        datepart(month,[entry_date]) EntryMonth,
        datepart(year,[entry_date]) EntryYear,
        [sumofentry_amount],
    FROM <yourtables and joins go here>
    WHERE dbo_OUTPAT_AREA_CDS.REG_AREA_TYPE)<>'RE'
        and datepart(year,[entry_date]) in (2011, 2012)
) src
group by CLINIC_ID, SUMMARY_CATGRY, ENTRY_TYPE

T-SQL IF statement embedded in a sum() function

T-SQL doesn't have a "inline" IF statement - use a CASE instead:

SELECT
    CMTS_RQ.[Dated],
    CMTS_RQ.CMTS_Name,
    Count(CMTS_RQ.CMTS_Name) AS emat_count,
    Sum(CASE 
           WHEN CMTS_RQ.US_Pwr >=37 AND CMTS_RQ.US_Pwr <= 49 
             THEN 1
             ELSE 0 
        END) AS us_pwr_good
FROM
    CMTS_RQ
GROUP BY
    CMTS_RQ.CMTS_Name,
    CMTS_RQ.[Dated]

So if the value of CMTS_RQ.US_Pwr is >= 37 AND <= 49 then add 1 to the SUM - otherwise 0. Does that give you what you're looking for?

In SQL Server 2012 and newer, you can use the new IIF function:

SUM(IIF(CMTS_RQ.US_Pwr >= 37 AND CMTS_RQ.US_Pwr <= 49, 1, 0)) AS us_pwr_good

Carrying out a SUMIF like operation using SQL Server Report Builder

The data type of the column 'kWp' is Decimal so you need to either convert the default value to 0.00 or cast the column to double

 SUM(iif(Fields!ProjectTypeID.Value = 2,cdbl(Fields!kWp.Value),0.00))

---

Related Topics