Help to do this query!!

  • hello SSC's Friends

    Today i was doing a query for store procedure and one of the query was this, i hope an enthusiastic guy could do it 😀

    Alright this is it:

    I got this data

    M-Is male or not

    F-Is female or not

    Age

    C-Category code

    M F Age C

    1 0 36 A

    1 0 36 A

    1 0 36 A

    1 0 28 A

    1 0 0 BB

    1 0 0 BB

    1 0 26 A

    1 0 113 P

    0 1 0 BB

    1 0 24 A

    1 0 35 A

    0 1 76 A

    1 0 7 NI

    1 0 22 A

    1 0 113 P

    1 0 24 A

    1 0 35 A

    1 0 24 A

    1 0 24 A

    1 0 7 NI

    1 0 54 A

    1 0 0 BB

    0 1 0 BB

    1 0 54 A

    0 1 0 BB

    1 0 31 A

    0 1 36 A

    0 1 23 A

    1 0 4 NI

    0 1 56 A

    1 0 52 A

    1 0 113 P

    0 1 37 A

    1 0 114 P

    1 0 1 BB

    0 1 6 NI

    0 1 39 A

    1 0 0 BB

    1 0 44 A

    0 1 0 BB

    1 0 51 A

    1 0 36 A

    1 0 0 BB

    1 0 35 A

    1 0 32 A

    0 1 28 A

    0 1 0 BB

    1 0 45 A

    1 0 44 A

    1 0 31 A

    1 0 22 A

    1 0 35 A

    0 1 36 A

    0 1 0 BB

    1 0 33 A

    1 0 33 A

    1 0 1 BB

    1 0 31 A

    1 0 33 A

    0 1 47 A

    1 0 38 A

    1 0 5 NI

    0 1 0 BB

    0 1 0 BB

    1 0 24 A

    1 0 28 A

    0 1 29 A

    1 0 32 A

    0 1 36 A

    0 1 23 A

    1 0 1 BB

    1 0 32 A

    1 0 36 A

    1 0 4 NI

    1 0 32 A

    1 0 24 A

    1 0 26 A

    1 0 32 A

    1 0 35 A

    1 0 33 A

    1 0 24 A

    1 0 5 NI

    0 1 0 BB

    1 0 5 NI

    0 1 0 BB

    1 0 44 A

    1 0 44 A

    1 0 32 A

    1 0 1 BB

    0 1 0 BB

    1 0 44 A

    1 0 0 BB

    0 1 0 BB

    1 0 24 A

    1 0 28 A

    1 0 32 A

    1 0 48 A

    The expected result set is this:

    C-Category code

    NM-Number of male

    NF-Number of female

    L-literal, it doesnt matter what is the value of this.

    C NM NF L

    A 51 12 A

    BB 10 12 A

    NI 7 1 A

    P 4 0 A

    so on, can any one send me a good query with nice performance rather than the one i did 😛

    BTW, i attached the script to populate the tbl_test for this.

    Enjoy it.

  • I forget something, is there any way to use windows functions on this example?

    thanks 😀

  • jaimepc199 (6/19/2014)


    I forget something, is there any way to use windows functions on this example?

    thanks 😀

    I am sure there is, but why complicate things when it is not necessary?

    select codcategory C

    , SUM(case when male = 1 then 1 else 0 end) NM

    , SUM(case when female = 1 then 1 else 0 end) NF

    , 'A' L

    from tbl_test

    group by codcategory

    As far as performance goes, I have no idea how this will do compared to what you have done since you did not post your original query!

    Also, performance will be greatly influenced by your indexes, keys and constraints. It is very difficult to say how fast it will run.

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Nice one!

    Indeed i guess that windows function would be better but like you say, usually we create a hyper mega cannon based on electron lasser to destroy an ant :w00t:

    So on, this is the query i did in the begin:

    Select

    'C'=codcategory,

    'NM'=(Select Count(c.Male) From tbl_test As c Where c.codcategory=t.codcategory And c.Male=1),

    'NF'=(Select Count(c.Female) From tbl_test As c Where c.codcategory=t.codcategory And c.Female=1),

    'L'='A'

    From tbl_test as t

    Group by codcategory

    I guess yours is simply

  • There's no need for window functions, they won't work better than a simple aggregate function.

    You don't need to write a complete CASE either. Here are two options to shorten the code assuming that your Male and Female columns are bit. If they're int (or any other numeric), you could just use SUM directly.

    SELECT codcategory AS C,

    SUM( SIGN(Male)) AS NM,

    SUM( SIGN(Female)) AS NF,

    'A' AS L

    FROM tbl_test

    GROUP BY codcategory;

    SELECT codcategory AS C,

    COUNT( NULLIF(Male,0)) AS NM,

    COUNT( NULLIF(Female,0)) AS NF,

    'A' AS L

    FROM tbl_test

    GROUP BY codcategory;

    By the way, you should facilitate things by giving DDL and sample data like this:

    CREATE TABLE tbl_test(

    Male bit,

    Female bit,

    Age int,

    codcategory char(2))

    INSERT INTO tbl_test VALUES

    (1, 0, 36, 'A'),

    (1, 0, 36, 'A'),

    (1, 0, 36, 'A'),

    (1, 0, 28, 'A'),

    (1, 0, 0, 'BB'),

    (1, 0, 0, 'BB'),

    (1, 0, 26, 'A'),

    (1, 0, 113, 'P'),

    (0, 1, 0, 'BB'),

    (1, 0, 24, 'A'),

    (1, 0, 35, 'A'),

    (0, 1, 76, 'A'),

    (1, 0, 7, 'NI'),

    (1, 0, 22, 'A'),

    (1, 0, 113, 'P'),

    (1, 0, 24, 'A'),

    (1, 0, 35, 'A'),

    (1, 0, 24, 'A'),

    (1, 0, 24, 'A'),

    (1, 0, 7, 'NI'),

    (1, 0, 54, 'A'),

    (1, 0, 0, 'BB'),

    (0, 1, 0, 'BB'),

    (1, 0, 54, 'A'),

    (0, 1, 0, 'BB'),

    (1, 0, 31, 'A'),

    (0, 1, 36, 'A'),

    (0, 1, 23, 'A'),

    (1, 0, 4, 'NI'),

    (0, 1, 56, 'A'),

    (1, 0, 52, 'A'),

    (1, 0, 113, 'P'),

    (0, 1, 37, 'A'),

    (1, 0, 114, 'P'),

    (1, 0, 1, 'BB'),

    (0, 1, 6, 'NI'),

    (0, 1, 39, 'A'),

    (1, 0, 0, 'BB'),

    (1, 0, 44, 'A'),

    (0, 1, 0, 'BB'),

    (1, 0, 51, 'A'),

    (1, 0, 36, 'A'),

    (1, 0, 0, 'BB'),

    (1, 0, 35, 'A'),

    (1, 0, 32, 'A'),

    (0, 1, 28, 'A'),

    (0, 1, 0, 'BB'),

    (1, 0, 45, 'A'),

    (1, 0, 44, 'A'),

    (1, 0, 31, 'A'),

    (1, 0, 22, 'A'),

    (1, 0, 35, 'A'),

    (0, 1, 36, 'A'),

    (0, 1, 0, 'BB'),

    (1, 0, 33, 'A'),

    (1, 0, 33, 'A'),

    (1, 0, 1, 'BB'),

    (1, 0, 31, 'A'),

    (1, 0, 33, 'A'),

    (0, 1, 47, 'A'),

    (1, 0, 38, 'A'),

    (1, 0, 5, 'NI'),

    (0, 1, 0, 'BB'),

    (0, 1, 0, 'BB'),

    (1, 0, 24, 'A'),

    (1, 0, 28, 'A'),

    (0, 1, 29, 'A'),

    (1, 0, 32, 'A'),

    (0, 1, 36, 'A'),

    (0, 1, 23, 'A'),

    (1, 0, 1, 'BB'),

    (1, 0, 32, 'A'),

    (1, 0, 36, 'A'),

    (1, 0, 4, 'NI'),

    (1, 0, 32, 'A'),

    (1, 0, 24, 'A'),

    (1, 0, 26, 'A'),

    (1, 0, 32, 'A'),

    (1, 0, 35, 'A'),

    (1, 0, 33, 'A'),

    (1, 0, 24, 'A'),

    (1, 0, 5, 'NI'),

    (0, 1, 0, 'BB'),

    (1, 0, 5, 'NI'),

    (0, 1, 0, 'BB'),

    (1, 0, 44, 'A'),

    (1, 0, 44, 'A'),

    (1, 0, 32, 'A'),

    (1, 0, 1, 'BB'),

    (0, 1, 0, 'BB'),

    (1, 0, 44, 'A'),

    (1, 0, 0, 'BB'),

    (0, 1, 0, 'BB'),

    (1, 0, 24, 'A'),

    (1, 0, 28, 'A'),

    (1, 0, 32, 'A'),

    (1, 0, 48, 'A');

    EDIT:

    I missed the file. I'm used to have the code in the post. :hehe:

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • jaimepc199 (6/19/2014)


    I forget something, is there any way to use windows functions on this example?

    thanks 😀

    As the set has only one divisor (codcategory) and there is no requirement to preserve the details, there is only one "window" used. Therefore there is no gain, only additional cost by using the window functions. Typically there would be a single table/covering index scan in both cases but the difference would be additional stream aggregation, scalar computation and nested loop for each computed column (NM,NF).

    The worst thing would be to combine the two, the cost would be the combined cost of both solutions minus one table scan. You can test this by running the code below with "show actual execution plan" set on.

    😎

    USE tempdb;

    GO

    /* GROUP BY */

    SELECT

    TT.codcategory AS C

    ,SUM(CAST(TT.male AS INT)) AS NM

    ,SUM(CAST(TT.Female AS INT)) AS NF

    ,'A' AS L

    FROM dbo.tbl_test TT

    GROUP BY TT.codcategory;

    /* Window Function */

    ;WITH DATA_BASE AS

    (

    SELECT

    TT.codcategory AS C

    ,ROW_NUMBER() OVER

    (

    PARTITION BY TT.codcategory

    /* This order clause does not create any work

    as there is already a Sort operator in the

    execution plan for the partitioning.

    Using (SELECT NULL) does not change the plan.

    */

    ORDER BY TT.codcategory

    ) AS C_RID

    ,SUM(CAST(TT.male AS INT)) OVER

    (

    PARTITION BY TT.codcategory

    ) AS NM

    ,SUM(CAST(TT.Female AS INT)) OVER

    (

    PARTITION BY TT.codcategory

    ) AS NF

    ,'A' AS L

    FROM dbo.tbl_test TT

    )

    SELECT

    DB.C

    ,DB.NM

    ,DB.NF

    ,DB.L

    FROM DATA_BASE DB

    WHERE DB.C_RID = 1;

    /* Combined */

    ;WITH DATA_BASE AS

    (

    SELECT

    TT.codcategory AS C

    ,SUM(CAST(TT.male AS INT)) OVER

    (

    PARTITION BY TT.codcategory

    ) AS NM

    ,SUM(CAST(TT.Female AS INT)) OVER

    (

    PARTITION BY TT.codcategory

    ) AS NF

    ,'A' AS L

    FROM dbo.tbl_test TT

    )

    SELECT

    DB.C

    ,DB.NM

    ,DB.NF

    ,DB.L

    FROM DATA_BASE DB

    GROUP BY DB.C,DB.NM,DB.NF,DB.L;

Viewing 6 posts - 1 through 5 (of 5 total)

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