Calculate Syntax

I just wanted some direction. I didn't ask for anyone to do any work for me.

>I didn't ask for anyone to do any work for me.
seriously, you did indeed not ask for anything...
just posted the assignment questions.

anyhow, the directions are this:
* you need some WHERE clause to limit the rows eventually
* you need some analytcal functions (like SUM, AVG, MIN, MAX ... ) to "calculate"
* you might need the GROUP BY syntax

Mybe I wasn't clear, but it's this website here to help?

>but it's this website here to help?
yes.

>Mybe I wasn't clear,
indeed, not clear.

you might want to review the site's guidelines, which states that direct "help" (aka giving solutions for homework & co) are not allowed.

so, as you asked, assistance is fine and will be given, but not the plain SQL.
do some work, post the sql you build, and we shall get you to the final solution.

So I need somthing like this;

SELECT exempt and non-exempt employees as "Total wages"
FROM Employee
GROUP BY Job_titlet;

I am not in school!!! I am trying to learn this on my own so I can add this to the thing that I can do.

ok, you start with question 1:
>1.      Calculate the total payroll of exempt and non-exempt employees

to solve this, you have to start identifying the entities from the sentence:
* employee   => table
* exempt/non-exempt  => column
* payroll   => column/table

so, start putting "names" on those entities (aka, clarify the table schema involved)

Sorry that I was looking for help!!! I want do that again...Have a nice day.

Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
    Chinese Proverb

Not sure why you are walking away (per your last comment), Tojo, as a3 appears to be ready to help guide you through to completion.  The advice he gave you is beyond the call of duty.  If we simply post code here, do you learn anything?  And if you don't, did we help?  Conversely, I guarantee you will learn a great deal from him if you go through the process with him step by step doing your part to actually do some of the work.  He is very good in the art of SQL if you are new here and have not yet familiarized yourself with him.  You will find no better mentor.  Given you have a number of questions already PAQ'd here but don't know where to start on this should be an indication that you need to pick a new approach and a3 is offering you that opportunity with not just one of but "the" best as your guide!

Just my personal opinion.  Take it for what it is worth to you...

Best regards and happy coding,
M-1

thanks for the support, M-1
please note that I am about to leave the keyboard for a couple of hours (getting some sleep :)
a3

mwvisa1 I did ask for help!!! The only way a person can learn is by doing. I want to learn and if he would have ask for me to go into more detail of my problem, I would have. An expert is defined  as a person who doesn't always have the answer.  I also know that I will always help anyone I can, because doing that will help  me to increase my knowledge. These traits, experience, knowing limits and learning through helping others, are what I think makes the core of an expert. An expert will not always have the answer, but if he or she works hard to found a good solution to the problem, then they would always be considered an expert.

Agreed.  a3 is definitely such a person, so again think there was just a miscommunication and I am glad you are still around as I was worried you had left.  We are tasked to uphold the members agreement and so the initial comments were not intended to indicate we would not help but to say that posting what appears to be a homework assignment with no specific question was inappropriate.

But, anyway, if you would like assistance in this matter, please provide the table structure.  Some sample data and your expected results with your exact issue with getting this done and we can help from there.

If you find that the 6 points above you have 6 different issues with, then you should break this up into different questions as previously advised.

However, after you provide more detail and what you have tried, you may be able to frame for us the common problem between all 6 and we can approach things from that standpoint.


Regards,
M-1

And actually before we go too far and forget, angel eyes actual gave you some directions here that should fit all 6 items.

http:#25724691

Ok I am tring to calculate the number of exempt and non-exempt employees there are, by job title.        and sorted the data in descending order. This is what I have.

SELECT COUNT (Exempt_Non_Exempt_Status) FROM Job_title j INNER JOIN
      Employee e ON j.Job_title = E.Job_title
ORDER BY Job_title DESC;

Is Exempt_Non_Exempt_Status a column in your job_title or employee table?

Tojo,

>  to calculate the number of exempt and non-exempt employees
as I don't know your table schema, you have to clarify, for me, how the "exempt" is defined in the data.
without that, I cannot "fix" your code, I can only see that it is incomplete, but that, you are aware yourself.

>by job title
you will need to GROUP BY by job title ... and add the column in the select list also

> and sorted the data in descending order
in descending order of "what"? in your sql, you order by the "job title", is that really the "requirement"?
usually, the request is like "order by the [count] descending". Anyhow, the ORDER BY is correct there.

>ON j.Job_title = E.Job_title
this sounds surprising... if the job_title is already in the employee table, why join to job_title table?
this can be answered by clarifying the table schema with the data...

Mybe seeing what I have will help.

CREATE TABLE Job_title
(
[Job_ID] int PRIMARY KEY IDENTITY(1,1),
[Job_title] varchar(50) NOT NULL,
[High_wage] money NOT NULL,
[Low_wage] money NOT NULL,
[Exempt_Non_Exempt_Status] varchar(20) NOT NULL,
)
GO

INSERT INTO Job_title
VALUES('Accounting Clerk',16.50,11.00,'Non-Exempt');
 
INSERT INTO Job_title
VALUES('Assistant Manager',57.00,33.00,'Exempt');
 
INSERT INTO Job_title
VALUES('Bagger',10.00,6.75,'Non-Exempt');
 
INSERT INTO Job_title
VALUES('Cashier',11.75,7.25,'Non-Exempt');
 
INSERT INTO Job_title
VALUES('Computer Support Specialist',24.50,13.75,'Non-Exempt');
 
INSERT INTO Job_title
VALUES('Dir. of Fin. & Acct.',120000,50000,'Exempt');
 
INSERT INTO Job_title
VALUES('Asst.-Bakery & Pastry',11.75,7.25,'Non-Exempt');
 
INSERT INTO Job_title
VALUES('Asst.-Butchers & Seafood Specialists',11.75,7.25,'Non-Exempt');
 
INSERT INTO Job_title
VALUES('Stocker',11.75,7.25,'Non-Exempt');
 

CREATE TABLE Employee
(
[SSN] varchar(9) not null primary key,
[LastName] varchar(25) NOT NULL,
[FirstName] varchar(25) NOT NULL,
[Address] varchar(40) NOT NULL,
[City] varchar(15) NOT NULL,
[State] char(2) NOT NULL,
[Telephone_area_code] varchar(3) NOT NULL,
[Telephone_number] varchar(8) NOT NULL,
[Zipcode] varchar(5) NOT NULL,
[Job_title] varchar(50) NOT NULL,
[DateOfBirth]datetime NOT NULL,
[Hire_date] datetime NOT NULL,
[Wage] money NOT NULL,
[Job_ID] int NOT NULL REFERENCES Job_title(Job_ID)
)
GO
 
   
INSERT INTO Employee
VALUES('555555551','Edleman','Glenn','175 Bishops Lane','LaJolla','CA',
     '619','555-0199','92037','Cashier','01/26/1965','10/7/2003',10.75,'1');
     
INSERT INTO Employee
VALUES('555555552','McMullen','Eric','763 Church St.','Lemon Grove', 'CA',
     '619','555-0133','91945','Bagger','03/16/1969','11/1/2002',6.75,'2');
     
INSERT INTO Employee
VALUES('555555553','Slentz', 'Raj','123 Torrey Dr.','Clairmont','CA','619','555-0123','91711',
     'Assistant Manager','05/26/1972','6/1/2000',48000,'3');
     
INSERT INTO Employee
VALUES('555555554','Broun','Erin','2045 Parkway Apt. 2B','Encinitas','CA','760',
    '555-0100','92024','Bagger','12/05/1966','3/12/2003',6.75,'4');
   
INSERT INTO Employee
VALUES('555555555','Carpenter','Donald','927 Second St.','Encinitas','CA','619',
    '555-0154','92024','Stocker','07/14/1967','11/1/2003',7.50,'5');
   
INSERT INTO Employee
VALUES('555555556','Esquivez','David','10983 N. Coast Hwy Apt. 902','Encinitas',
    'CA','760','555-0108','92024','Asst.-Butchers & Seafood Specialists','06/26/1970',
    '7/25/2003',9.25,'6');
   
INSERT INTO Employee
VALUES('555555557','Sharp','Nancy','10793 Montecino Rd.','Ramona','CA',
    '858','555-0135','92065','Cashier','09/09/1968','7/12/2003',10.50,'7');
   
INSERT INTO Employee
VALUES('555555558','Avery','Ledonna','198 Governor Dr.','Del Mar','CA',
   '619','555-0135','92014','Asst.-Bakery & Pastry','01/31/1965','3/01/2003',11.00,'8');
   
INSERT INTO Employee
VALUES('555555559','Tyink','Thomas','87592 Pacific Heights BLVD.','Del Mar','CA',
   '858','555-0159','92014','Asst.-Bakery & Pastry','11/26/1966','4/01/2001',9.75,'9');

Select * from employee
Select * from job_title
SELECT 'FirstName', 'LastName' FROM Employee

SELECT * FROM Job_title j INNER JOIN
      Employee e ON j.Job_ID = E.Job_ID
WHERE j.High_wage BETWEEN $6.00 AND $18.00

SELECT * FROM Job_title j INNER JOIN
      Employee e ON j.Job_ID = E.Job_ID
WHERE e.Zipcode IN ('91945', '92024')

SELECT * FROM Job_title j INNER JOIN
      Employee e ON j.Job_ID = E.Job_ID
WHERE e.Telephone_area_code LIKE '619%' OR  e.Telephone_area_code LIKE '858%'

UPDATE Employee SET DateOfBirth='12/23/2009' WHERE SSN = 555555555

SELECT * FROM Employee WHERE DateDiff(Year, DateOfBirth, GetDate() )  > 40

SELECT LastName FROM Employee ORDER BY Job_title

SELECT * FROM Job_title j INNER JOIN
      Employee e ON j.Job_ID = E.Job_ID
ORDER BY J.High_wage

in descending order of "what"? in your sql, you order by the "job title", is that really the "requirement"?

No, I wanted to see if it can be done and the descending order can be by Job_ID

Thats works. Thanks for your help!!!