Query help

Question

Post reply

Query help

praveensc2021

Old Hand

Points: 333
More actions
December 2, 2021 at 7:03 pm

#3959689

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
December 4, 2021 at 10:06 am

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

ratbak Ten Centuries Points: 1379 More actions · Answer 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.

Mike01 SSChampion Points: 11395 More actions · Answer 2

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/

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

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.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning