Various Aggregate Functions in a same SQL Statement

  • Hi All,

    Need your advise to improve my SQL, please. The following statement works well indeed, but

    I am not sure if it will work in a same fast way, when the data will grow up.

    There is a list of products (in my example below - myMainTable) and their sales history (myDataTable).

    I need to build a report to show per each product (obj_id) number of sales ( a simple COUNT will do), total income

    from the sales and number of units sold (quantity). The following works OK, but I use DISTINCT and don't feel comfortable

    with future performance.

    Thank you in advance!

    WITH myMainTable AS

    (SELECT 1 AS obj_id, 'A' AS obj_desc

    UNION ALL

    SELECT 2,'B'

    UNION ALL

    SELECT 3,'C'

    UNION ALL

    SELECT 4,'D'

    UNION ALL

    SELECT 5,'E'

    UNION ALL

    SELECT 6,'F'),

    myDataTable AS (

    SELECT 1 AS obj_id,

    3 AS qnty,

    45 AS total_price

    UNION ALL

    SELECT 1,4,87

    UNION ALL

    SELECT 1,1,10

    UNION ALL

    SELECT 1,40,751

    UNION ALL

    SELECT 2,10,8

    UNION ALL

    SELECT 2,100,87

    UNION ALL

    SELECT 3,6,9)

    SELECT DISTINCT

    m.obj_id,

    m.obj_desc,

    SUM (d.total_price) OVER (PARTITION BY m.obj_id) AS total_income,

    SUM (d.qnty) OVER (PARTITION BY m.obj_id) AS total_qnty,

    COUNT(d.obj_id) OVER (PARTITION BY m.obj_id) AS total_sales

    FROM myMainTable m

    JOIN myDataTable d

    ON d.obj_id = m.obj_id

  • You don't need to use the over clause. You can just use group by instead:

    WITH myMainTable AS

    (SELECT 1 AS obj_id, 'A' AS obj_desc

    UNION ALL

    SELECT 2,'B'

    UNION ALL

    SELECT 3,'C'

    UNION ALL

    SELECT 4,'D'

    UNION ALL

    SELECT 5,'E'

    UNION ALL

    SELECT 6,'F'),

    myDataTable AS (

    SELECT 1 AS obj_id,

    3 AS qnty,

    45 AS total_price

    UNION ALL

    SELECT 1,4,87

    UNION ALL

    SELECT 1,1,10

    UNION ALL

    SELECT 1,40,751

    UNION ALL

    SELECT 2,10,8

    UNION ALL

    SELECT 2,100,87

    UNION ALL

    SELECT 3,6,9)

    SELECT m.obj_id,

    m.obj_desc,

    SUM (d.total_price) as total_income,

    SUM (d.qnty) AS total_qnty,

    COUNT(d.obj_id) AS total_sales

    FROM myMainTable m

    JOIN myDataTable d

    ON d.obj_id = m.obj_id

    group by m.obj_id, m.obj_desc

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Oh no!!! Of course! Thanks, Adi!

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

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