Need help on dividing two values of the same column/grouping

  • Hello,

    I'm trying to divide two values from separate rows. Each row is a separate UNION statement.

    2014-08-03 00:00:00.000NKBB (N) - Total Offers 1218 UNION (A)

    2014-08-03 00:00:00.000NKBB (N) - With Lead 301 UNION (B)

    2014-08-03 00:00:00.000NKBB (N) - Without Leads 917 UNION (C)

    In the below example, I would like to divide KBB (N) - With Lead (UNION (B)/KBB (N) - Total Offers UNION (A)

    What would be the best way to accomplish this?

    Thank you for your answers.

  • PFlorenzano-641896 (8/4/2014)


    Hello,

    I'm trying to divide two values from separate rows. Each row is a separate UNION statement.

    2014-08-03 00:00:00.000NKBB (N) - Total Offers 1218 UNION (A)

    2014-08-03 00:00:00.000NKBB (N) - With Lead 301 UNION (B)

    2014-08-03 00:00:00.000NKBB (N) - Without Leads 917 UNION (C)

    In the below example, I would like to divide KBB (N) - With Lead (UNION (B)/KBB (N) - Total Offers UNION (A)

    What would be the best way to accomplish this?

    Thank you for your answers.

    Hey there!

    So, first, it'd be great if you could have a read of this (http://www.sqlservercentral.com/articles/Best+Practices/61537/)[/url], then set up some sample data, DDL and expected results based on your sample data. Otherwise, we're guessing at data-types, where your data comes from etc etc. You say that your data is from a UNION query, are all of the UNIONs querying the same tables but with a slightly different WHERE clause? If so, it's likely that we can help you to improve the query and make this whole job a little simpler in the process 🙂

    For now, an initial (and pretty poor) way to do what you've requested would be something like this: -

    WITH CTE ([WHATEVER], [ANOTHER THING], [SOMETHING ELSE], [A NUMBER THIS TIME!]) AS

    (

    SELECT '2014-08-03 00:00:00.000','N','KBB (N) - Total Offers',1218 UNION

    SELECT '2014-08-03 00:00:00.000','N','KBB (N) - With Lead',301 UNION

    SELECT '2014-08-03 00:00:00.000','N','KBB (N) - Without Leads',917

    )

    SELECT [WHATEVER], [ANOTHER THING], [SOMETHING ELSE], [A NUMBER THIS TIME!],

    ([A NUMBER THIS TIME!]*1.0)/[Total Offers] AS [DIVIDE]

    FROM CTE a

    OUTER APPLY (SELECT [A NUMBER THIS TIME!]

    FROM CTE b

    WHERE a.[SOMETHING ELSE] = 'KBB (N) - With Lead'

    AND b.[SOMETHING ELSE] = 'KBB (N) - Total Offers'

    ) ca([Total Offers]);

    Which returns: -

    WHATEVER ANOTHER THING SOMETHING ELSE A NUMBER THIS TIME! DIVIDE

    ----------------------- ------------- ----------------------- ------------------- ---------------------------------------

    2014-08-03 00:00:00.000 N KBB (N) - Total Offers 1218 NULL

    2014-08-03 00:00:00.000 N KBB (N) - With Lead 301 0.247126436781

    2014-08-03 00:00:00.000 N KBB (N) - Without Leads 917 NULL


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply