Query help

  • create table assessments (

    id integer not null,

    experience integer not null,

    sql integer,

    algo integer,

    bug_fixing integer,

    unique(id)

    );

    INSERT INTO assessments

    VALUES(1, 3, 100, NULL, 50)

    INSERT INTO assessments

    VALUES(2, 5,NULL,100,100)

    INSERT INTO assessments

    VALUES(3,1,100,100,100)

    INSERT INTO assessments

    VALUES(4, 5,100,50,NULL)

    INSERT INTO assessments

    VALUES(5,5,100,100,100)

    I need the query to return

    xp | max | count

    -----+-----+-------

    5 | 2 | 3

    3 | 0 | 1

    1 | 1 | 1

  • max doesn't appear to directly correlate to values in any of the columns What is max/how is it derived?

    xp is apparently experience, and count is # of rows for experience value.

  • Not sure what you are asking.  Max of what??

    For better, quicker answers, click on the following...
    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/

  • Please read any book on basic data modeling or SQL. By definition, a table must have a key. A key is a particular subset of attributes in that table that are unique. Since an identifier exist on a nominal scale, it cannot be a numeric value; you do not get do calculations on it. A proper column name consist of <Attribute name>_< Attribute property>. I’m going to assume that the last three columns in this table declaration are counts of some kind. I also don’t see any need to make them NULL. Yes, I voted for Nels when I was in ANSI X3 H2 and I show how to use them in my books, but I really think they should be minimized in the DDL.

    People who believe in a universal “id” on a table are called idiots in SQL slang :-). An identifier must identify something in particular and cannot be a general, vague Kabbalah number.

    Let me try and clean up your code.

    CREATE TABLE Assessments

    (assessment_id CHAR(5) NOT NULL PRIMARY KEY,

    experience_level INTEGER NOT NULL,

    sql_stmt_cnt INTEGER NOT NULL DEFAULT 0,

    algo_cnt INTEGER NOT NULL DEFAULT 0,

    bug_fix_cnt INTEGER NOT NULL DEFAULT 0);

    Did you know that for the past several years you been able to use a table constructor. The values clause of an insertion statement? The old row–at–a-time Is how we used to have to do it with punchcards over 50 years ago. The table constructor can be optimized.

    INSERT INTO Assessment

    VALUES(1, 3, 100, NULL, 50),

    (2, 5, NULL, 100, 100),

    (3, 1, 100, 100, 100),

    (4, 5, 100, 50, NULL),

    (5, 5, 100, 100, 100);

    >> I need the query to return..<<

    There is nothing named “XP” in the table. Max () and count () are aggregate functions in SQL so they need a parameter. Your request makes no sense. Would you like to try again?

     

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Sounds like you're looking at how GROUP BY works. Combine that with MAX() and COUNT() and you should be good to go. I'm guessing the column you want to group by is Experience.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • This was removed by the editor as SPAM

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

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