CODEWITHSUNDEEP
sqlsql-serversql-server-2008
CodeCook
CodeCook

Reputation: 188

grouping the same value in upper case and lower case with select query

In the below table InputParameterName column contains Product1 ( P upper case) and pRoduct1 ( R upper case).

Table1 : 

InputParameterName  Period  PeriodInput
Product1             2017   25704
Product1             2018   25704
pRoduct1             2017   16
pRoduct1             2018   16
pRoduct1             2017   57.6
pRoduct1             2018   57.6
pRoduct1             2017   40.5
pRoduct1             2018   40.5

My query: 

SELECT InputParameterName, Period, SUM(CAST(PeriodInput AS FLOAT)) PeriodInput  
FROM Table1         
GROUP BY InputParameterName, Period

Result : 

InputParameterName  Period  PeriodInput
Product1                    2017    25818.1
pRoduct1                    2018    25818.1

In the result you can see InputParameterName column has Product1 ( P upper case) and pRoduct1 ( R upper case).

Is there any way to get both as same, either Product1 or pRoduct1 (exact values without applying any character case conversion) ?

Upvotes: 2

Views: 13567

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Use lower() or upper():

SELECT MIN(InputParameterName) as InputParameterName, Period,
       SUM(CAST(PeriodInput AS FLOAT)) PeriodInput  
FROM Table1         
GROUP BY LOWER(InputParameterName), Period;

The use LOWER() in the GROUP BY defines the columns. It is not used in the SELECT so you always get a value in the data.

If you don't care about the case, then you should look into setting the collation of the column, table, or database so case is ignored in expressions involving strings.

EDIT:

If the above result returns two rows, then case is not the problem. You probably have hidden characters. the most likely are spaces, so you can try:

GROUP BY LOWER(REPLACE(InputParameterName, ' ', '')), Period;

Hopefully, the hidden spaces are no more complicated than spaces.

EDIT II:

I get it now. You want the two rows, you just want the values to be the same. Then window functions can do what you want:

SELECT MIN(InputParameterName) OVER (PARTITION BY LOWER(InputParameterName)),
       Period, SUM(CAST(PeriodInput AS FLOAT)) as PeriodInput  
FROM Table1         
GROUP BY InputParameterName, Period;

Upvotes: 5

user7715598
user7715598

Reputation:

BY Using Row_Number() and Partitioning over 'Period 'Column you get desired result 

;With cte(InputParameterName,Period,PeriodInput)
    AS
    (
    SELECT 'Product1', 2017,25704 Union all
    SELECT 'Product1', 2018,25704 Union all
    SELECT 'product1', 2017,16    Union all
    SELECT 'product1', 2018,16    Union all
    SELECT 'product1', 2017,57.6  Union all
    SELECT 'product1', 2018,57.6  Union all
    SELECT 'product1', 2017,40.5  Union all
    SELECT 'product1', 2018,40.5
    )
    SELECT InputParameterName,Period,PeriodInput FRom
    (
    SELECT  InputParameterName,
            Period,
            CAST(SUM(PeriodInput)OVER(Partition by Period Order BY Period) AS FLOAT)AS PeriodInput,
            ROW_NUMBER()OVER(Partition by Period Order BY Period)AS Seq From cte    
    )DT
    WHERE DT.Seq=1

OutPut

InputParameterName  Period  PeriodInput
--------------------------------------
product1            2017    25818.1
product1            2018    25818.1

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

Use LOWER():

SELECT
    LOWER(InputParameterName) AS InputParameterName,
    Period,
    SUM(CAST(PeriodInput AS FLOAT)) PeriodInput  
FROM Table1         
GROUP BY
    LOWER(InputParameterName),
    Period

To be strictly ANSI compliant, we should not use a function of a column in the GROUP BY clause. In this case, we could use a derived table:

WITH cte AS (
    SELECT
        LOWER(InputParameterName) AS InputParameterName,
        Period,
        PeriodInput
    FROM Table1
)
SELECT
    InputParameterName,
    Period,
    SUM(CAST(PeriodInput AS FLOAT)) PeriodInput  
FROM cte       
GROUP BY
    InputParameterName,
    Period

Upvotes: 2

Related Questions