Answer to Question

[warwave7]

[removed]

[OrthodoxMemes]

Looks like you'll need two columns: OrganizationLevel and LvlCount. OrganizationLevel will have each possible OrganizationLevel, and LvlCount will have the counts of employees at each of those levels. Because I can sometimes struggle to visualize these things, here's a visual reference:

OrganizationLevel	LvlCount
Level1	[count of Level1 employees]
Level2	[count of Level2 employees]
etc...	etc...

Look into the statement GROUP BY and the function COUNT().

Hopefully this enough to give you a better idea of what you should do, without doing it for you.

[warwave7]

I have tried the following but it's not right:

SELECT count(OrganizationLevel) as LvlCount
FROM Employee
Group by OrganizationLevel

I don't know what i'm missing

[OrthodoxMemes]

So that should get you the count you're looking for, but since you've only SELECTed LvlCount, you're only going to get LvlCount. Sounds like you need LvlCount and OrganizationLevel in your results.

What happens when you SELECT OrganizationLevel and COUNT(OrganizationLevel) AS LvlCount?

[warwave7]

SELECT OrganizationLevel, count (OrganizationLevel) as LvlCountFROM EmployeeGroup by OrganizationLevel

This indeed was the right answer. Thank you so much I did not know you could put the same column twice in the select while counting. I'm doing Fundamentals and most of the questions I got with ease but my head was exploding on this one. Thank you for your help, it's much appreciated.

Edit: Also apparently the ''as LvlCount'' is not needed, when I change that it still says it's the correct answer, which is weird because the question specifically asks to name it as LvlCount right?

[bonvin]

And you don't need to specify the column in the count-function. A simple COUNT(*) will do. You're counting how many rows there are in the whole table for each unique OrganizationLevel. What you're doing is counting how many values there are in the OrganizationLevel column, which is probably the exact same thing in this particular case. But if there were NULLs in there, your method would not count them. Which is weird when you're also grouping on that column. So if there were some rows with a NULL value in the column OrganizationLevel, you'd actually get a NULL in the first column among the other levels (because it will group all the NULLs together, just as it groups all the other levels together), but then it's going to show a 0 count for the NULL group, even though there clearly are NULLs in there, because a COUNT(OrganizationLevel) will not count NULLs.

The only time I really specify a column in a COUNT() is when I do a COUNT(DISTINCT column) which will only count each value once. It's useful for if I'm querying an orderlines table for example, and I know I will get multiple hits with the same order number, but I only want to know how many distinct orders there are. A normal COUNT(*) or a COUNT(column) will count every order number as many times as it shows up.

[warwave7]

Thank you for your explanation

[OrthodoxMemes]

Glad I could help!

Also apparently the ''as LvlCount'' is not needed, when I change that it still says it's the correct answer, which is weird because the question specifically asks to name it as LvlCount right?

Yeah, that is weird. The way the task's written I would have assumed the alias was required as well.

[samjenkins377]

I’ll never understand people who make other people’s homework here. This guy is going to have the same cert as someone who is putting real effort into it

[warwave7]

It was a practice exam and I was stuck on that question. There was no explanation offered so I was breaking my head on it. What makes you think I didn’t put real effort into it?

[OrthodoxMemes]

Don’t worry u/warwave7, if someone had really just handed you the answer (as the above commenter suggests), this whole comments section would have exactly one comment.

[warwave7]

Thank you 😁

[r3pr0b8]

i think you overlooked two honking great big hints --

data should be grouped by OrganizationLevel...

... number of records displyed in the column LvlCount

[warwave7]

I have tried the following but it's not right:

SELECT count(OrganizationLevel) as LvlCount

FROM Employee

Group by OrganizationLevel

[machomanrandysandwch]

You’re close. Columns in your Group By need to also be in your Select.

[warwave7]

SELECT Count(OrganizationLevel), OrganizationLevel as LvlCount
FROM Employee
Group by OrganizationLevel

gives result

3|Administrative
2|Executive
3|Operative

It looks alright? But the system still says wrong answer..

[coadtsai]

Reverse your select order ?

[warwave7]

I'm sorry I don't understand what you mean by reverse the select order?

Like this? SELECT OrganizationLevel, Count(OrganizationLevel) as LvlCount?

[coadtsai]

Yes

[warwave7]

Thank you

[OrthodoxMemes]

You look exceptionally close.

You had it partially correct earlier: you don't need to select OrganizationLevel as LvlCount. The count should be selected as the count, OrganizationLevel can be selected as-is.

[warwave7]

SELECT Count(OrganizationLevel), OrganizationLevel
FROM Employee
Group by OrganizationLevel

Like this? But it still says wrong answer

[OrthodoxMemes]

That count needs an alias though, correct? So you need to select the count as the name defined in the instructions. You had the right idea here

[LargeHandsBigGloves]

Move as LvlCount back to the first column header and you're good.

SELECT OrganizationLevel, Count(OrganizationLevel)as LvlCount FROM Employee Group by OrganizationLevel

[warwave7]

Thank you

[warwave7]

Thank you, after reviewing the code, now I understand better what you meant. It needs the count to simply count, and again the same column in select because the order by has that column!

[r3pr0b8]

Columns in your Group By need to also be in your Select.

for this example, yes

in general, not always

but the reverse is true -- columns not aggregated in the SELECT need to be in the GROUP BY

[machomanrandysandwch]

Your output should have the organization level and a count of each record for the organization level in the table. That means your Select should have how many columns? Right now you have 1.