MattB February 2016

SQL Server: Group Query Based on Substring of Column

I need to get a substring from a column 'source' from typeTable and be able to get stats on each region from that one column. A row would look something like 'server001[en-US]'. Then print the stats for each country. That is, I need the countForType and totalForType for each country.

So, I believe get all server001 calls and group them by country is what I'm looking for.

My query, so far, looks like this:

        use thedatabase;

        declare @fromDate datetime = '2016-02-01 00:00:00.000';
        declare @toDate datetime = '2016-02-02 23:59:59.999';
        declare @source varchar(15) = 'server001';

        DECLARE @countForType bigint;
        DECLARE @totalForType decimal(30,8);

        DECLARE @country varchar(10);

        SELECT  @countForType = count(*),
                @totalForType = SUM(typeTable.amount),
                @country = 
                    case
                    when (charindex('[', typeTable.source) > 0 and charindex(']', typeTable.source) > 0)
                    then substring(typeTable.source, charindex('[', typeTable.source) +1, (charindex(']', typeTable.source) - 1) - charindex('[', typeTable.source))
                    else null
                    end

        FROM theTypeTable typeTable (nolock)
        WHERE typeTable.startDate > @fromDate
        AND typeTable.startDate < @toDate
        AND typeTable.source like @source 
        GROUP BY typeTable.source;  -- i believe the issue may be here -- source is the entire string 'server001[en-US]'.  I need to group and provide stats per country, which is a substring of source.


    --Print report:
    PRINT 'countForType: ' + CAST(@countForType AS VARCHAR);
    PRINT 'totalForType: ' + CAST(@totalForType AS VARCHAR);
    --for each country, print the amounts/ percentages etc...
    PRINT 'country: ' + CAST (@country AS VARCHAR);

The report itself would look something like:

countForType: 104
totalForT        

Answers


Tab Alleman February 2016

You already had the code. GROUP BY the CASE statement you used to get country:

GROUP BY                     case
                    when (charindex('[', typeTable.source) > 0 and charindex(']', typeTable.source) > 0)
                    then substring(typeTable.source, charindex('[', typeTable.source) +1, (charindex(']', typeTable.source) - 1) - charindex('[', typeTable.source))
                    else null
                    end

And by the way, your code will not do what your comments say you want it to do:

--Print report:
PRINT 'countForType: ' + CAST(@countForType AS VARCHAR);
PRINT 'totalForType: ' + CAST(@totalForType AS VARCHAR);
--for each country, print the amounts/ percentages etc...
PRINT 'country: ' + CAST (@country AS VARCHAR);

You are storing data in scalar variables, which means the execution of your query will only store one value into each of the variables. It will not allow you to loop through each result. To get the output in the exact format you specified in your question, you will need to use either a WHILE loop or a CURSOR.

However, I question whether you really need SQL to output that format. It would be better to let SQL return a result set, and format the output in your front end application.

Post Status

Asked in February 2016
Viewed 2,002 times
Voted 4
Answered 1 times

Search




Leave an answer