SQL query to SUM based on field values

Transact-SQL
1

Hello,

I am trying to figure out how to query to get the Sum result set daily.

Basically there are three fields on below query:

Declare @StartDate datetime
Declare @EndDate datetime

Set @StartDate = '06/13/2016'
Set @EndDate = '06/13/2016'

SELECT Column1, Column2, Column3, Date
From TBLEMPLOYEE
WHERE Date between @StartDate and @EndDate

So, the Column1 has more than 1500 distinct values and Column1 returns different values daily. Some of the examples of the values of Column1 like:
ABC, FGH, GTY, LKO. etc.

Questions:

  1. How do I SUM the value based on the report value (ex: ABC, FGH, GTY, etc)? Since the value is not the INT but varchar.

  2. Report runs daily so the values of Column1 is different daily. Today the value could be ABC, FGH, GTY, LKO, etc. Tomorrow
    could be NNM,KMM,VBF, ETC and so on.
    If the report has ABC value for example, I want to Sum of how many of that ABC value. The same thing if the report
    has the value of FGH, I want to SUM of how many the FGH values are on the report on that day and so on and at the end, I only need to list the the top 5 highest of SUM from Column1.

How do I develop this logic based on above query?

2 
SELECT Column1, COUNT(Column1), Date
From TBLEMPLOYEE
WHERE Date between @StartDate and @EndDate 
Group By Column1, Date
4

From your description you are looking to pull the total of each distinct value, rather than the number of distinct values. I believe the query below may be a good place to start. I began writing this knowing you wanted to pull the top 5 of each column. I didn't get that far and its a bit late. I hope this helps get you started, though (might be slight overkill?):

SELECT
    CASE
        WHEN columnNum = 1
            THEN info.[value]
    END AS 'column1'
    ,CASE
        WHEN columnNum = 1
            THEN info.valueCount
    END AS 'column1Count'
    ,CASE
        WHEN columnNum = 2
            THEN info.[value]
    END AS 'column2'
    ,CASE
        WHEN columnNum = 2
            THEN info.valueCount
    END AS 'column2Count'
    ,CASE
        WHEN columnNum = 3
            THEN info.[value]
    END AS 'column3'
    ,CASE
        WHEN columnNum = 3
            THEN info.valueCount
    ,date
FROM (
    SELECT
        '1'                           AS 'columnNum'
        ,column1                      AS 'value'
        ,COALESCE(emp1.value1Count,0) AS 'valueCount'
    FROM tblEmployee emp
        OUTER APPLY (
            SELECT COUNT(DISTINCT tblEmployee1.column1) AS 'value1Count'
            FROM tblEmployee tblEmployee1
            WHERE emp.column1=tblEmployee1.column1
        ) emp1
    UNION ALL
    SELECT
        '2'
        ,column2
        ,COALESCE(emp2.value2Count,0)
    FROM tblEmployee emp
        OUTER APPLY (
            SELECT COUNT(DISTINCT tblEmployee2.column2)  AS 'value2Count'
            FROM tblEmployee tblEmployee2
            WHERE emp.column2=tblEmployee2.column2
        ) emp2
    UNION ALL
    SELECT
        '3'
        ,column3
        ,COALESCE(emp3.value3Count,0)
    FROM tblEmployee emp
        OUTER APPLY (
            SELECT COUNT(DISTINCT tblEmployee3.column3) AS 'value3Count'
            FROM tblEmployee tblEmployee3
            WHERE emp.column3=tblEmployee3.column3
        ) emp3
) info
WHERE (1=1)
    AND (date BETWEEN '06/13/2016' AND '06/13/2016')
GROUP BY
    info.columnNum
    ,info.[value]
    ,info.valueCount
ORDER BY
    info.columnNum
    ,info.valueCount
5

Did this work out for you?