Need to write SQL Server query to return sum of unique values (based on one column)

Question

My table looks like this:

Supplier    Reference       Description      Total    
--------------------------------------------------
smiths      BP657869510L    NULL             42
smiths      BP657869510L    NULL             42
smiths      BP654669510L    No. 5621         13
smiths      BP654669510L    No. 5621         13
corrigan    15:51           Order 23542      23
corrigan    15:51           Order 23542      23
williams    14015           Block B          19
williams    14015           Block B          19

I would like to write a T-SQL query to

• return the list of transactions with each supplier, eliminating duplicate entries based on the Reference column.
• return the total sum of transactions with each supplier, again eliminating duplicate entries based on the Reference column.

So the results I would want to return based on the data above would be

    Supplier    Reference       Description      Total    
    ---------------------------------------------------
    smiths      BP657869510L    NULL             42
    smiths      BP654669510L    No. 5621         13
    corrigan    15:51           Order 23542      23
    williams    14015           Block B          19

and for the second requirement:

    Supplier    Total  
    ---------------------  
    smiths      55
    corrigan    23
    williams    19

Is this possible? Please note that values in other columns may differ even though the Reference column contains the same value. It doesn't matter if this occurs, I am only concerned with rows which contain a distinct or unique Reference value.

Answer 1

Try below sql
assuming @tempData is your table name.

declare @tempData table
(
    supplier nvarchar(20),
    reference nvarchar (20),
    xDescription nvarchar(20),
    total int
);

insert into @tempData
select 'smiths',      'BP657869510L'    ,NULL,             42 union all
select 'smiths',      'BP657869510L'    ,NULL,             42 union all
select 'smiths',      'BP654669510L'    ,'No. 5621',         13 union all
select 'smiths',      'BP654669510L'    ,'No. 5621',         13 union all
select 'corrigan',    '15:51'           ,'Order 23542',      23 union all
select 'corrigan',    '15:51'           ,'Order 23542',      23 union all
select 'williams',    '14015'           ,'Block B',          19 union all
select 'williams',    '14015'           ,'Block B',          19
;

select 
    a.supplier
    , a.reference
    , a.xDescription
    , a.total
from @tempData a
group by a.supplier 
    , a.reference
    , a.xDescription
    , a.total
;

/*
supplier             reference            xDescription         total
-------------------- -------------------- -------------------- -----------
corrigan             15:51                Order 23542          23
smiths               BP654669510L         No. 5621             13
smiths               BP657869510L         NULL                 42
williams             14015                Block B              19
*/


with cte as
(
select 
    a.supplier
    , a.reference
    , a.xDescription
    , a.total
from @tempData a
group by a.supplier 
    , a.reference
    , a.xDescription
    , a.total
) 
select 
    distinct c.supplier, sum(c.total) over(partition by c.supplier) as total
from cte c
;

/*
supplier             total
-------------------- -----------
corrigan             23
smiths               55
williams             19
*/

UPDATE
as requested, the aim for this query is to include Separate record that has the same supplier with different description: example supplier smith

Dense_Rank() will fulfill this request (http://technet.microsoft.com/en-us/library/ms173825(v=sql.90).aspx)

with cte as
(
select 
    a.supplier
    , a.reference
    , a.xDescription
    , a.total
    ,dense_rank() over(partition by a.supplier order by a.supplier, a.xDescription) as dRow
from @tempData a
group by a.supplier 
    , a.reference
    , a.xDescription
    , a.total
) 
select 
    distinct c.supplier, sum(c.total) over(partition by c.supplier,drow) as total
from cte c
;

/*
supplier             total
-------------------- -----------
corrigan             23
smiths               13
smiths               42
williams             19
*/

View All field

with cte as
(
select 
    a.supplier
    , a.reference
    , a.xDescription
    , a.total
    ,dense_rank() over(partition by a.supplier order by a.supplier, a.xDescription) as dRow
from @tempData a
group by a.supplier 
    , a.reference
    , a.xDescription
    , a.total
) 
select 
    distinct c.supplier, c.reference,c.xDescription, sum(c.total) over(partition by c.supplier,drow) as total
from cte c
;

Answer 2

As per a comment from the OP Total is always the same for Reference, but Description can change. DISTINCT is equivalent to a GROUP BY all the columns in the SELECT

To get the first requirement a distinct is enough, if it's possible to drop the Description column

SELECT DISTINCT 
       Supplier
     , Reference
     , Total
FROM   myTable

if it's not possible then a NULL, a MAX or something on the same line can be done, in the query below a NULL is returned if there are more then one values for the group, otherwise the single value is outputted

SELECT Supplier
     , Reference
     , Description = CASE WHEN COUNT(DISTINCT Description) > 1 THEN NULL
                          ELSE MAX(Description)
                     END
     , Total
FROM   myTable
GROUP BY Supplier, Reference, Total

To get the second the above query can be used as a CTE for the main query where a GROUP BY is added, in this case the Description columns is not needed so is dropped.

With dValue AS (
  SELECT DISTINCT 
         Supplier
       , Reference
       , Total
  FROM   myTable
)
SELECT Supplier
     , SUM(Total) Total
FROM   dValue
GROUP BY Supplier

If you have a version of SQLServer where the CTE are not possible the first query can be used as a subquery to get the same result

SELECT Supplier
     , SUM(Total) Total
FROM   (SELECT DISTINCT Supplier, Reference, Total
        FROM   myTable) dValue
GROUP BY Supplier

Answer 3

declare @tempData table
(
    supplier nvarchar(20),
    reference nvarchar (20),
    xDescription nvarchar(20),
    total int
);

insert into @tempData
select 'smiths',      'BP657869510L'    ,NULL,             42 union all
select 'smiths',      'BP657869510L'    ,NULL,             42 union all
select 'smiths',      'BP654669510L'    ,'No. 5621',         13 union all
select 'smiths',      'BP654669510L'    ,'No. 5621',         13 union all
select 'corrigan',    '15:51'           ,'Order 23542',      23 union all
select 'corrigan',    '15:51'           ,'Order 23542',      23 union all
select 'williams',    '14015'           ,'Block B',          19 union all
select 'williams',    '14015'           ,'Block B',          19
;

select distinct x.supplier,
SUM(X.total)OVER(PARTITION BY x.supplier )As Total from 
(Select a.supplier,a.reference,a.xDescription,a.total from @tempData a
GROUP BY a.supplier,a.reference,a.xDescription,a.total) X 
GROUP BY x.supplier,X.total