SQL Query

Question

SQL Query

dreamslogic

Ten Centuries

Points: 1131
More actions
September 17, 2009 at 3:00 am

#385382

Hi Guys,
This is my first project can anyone help me by providing SQL query, how to generate below questions. They are three questions a,b and c.
Tblconsignment
Job_no, job_type, book_date, cust_code, depot, revenue, manifestexpenses
A)No of Consignments in the table. If NULL represent as ‘unknown’.
B)Total revenue on consignments
C)Total revenue per consignment
Thanks,
Roman

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

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

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

dreamslogic (9/17/2009)
Hi Guys,
This is my first project can anyone help me by providing SQL query, how to generate below questions. They are three questions a,b and c.
Tblconsignment
Job_no, job_type, book_date, cust_code, depot, revenue, manifestexpenses
A)No of Consignments in the table. If NULL represent as ‘unknown’.
B)Total revenue on consignments
C)Total revenue per consignment
Thanks,
Roman

Is Job_No unique? When you say consignment, is it the job_no or job_type? some test data, and your expected result would be helpful.

---------------------------------------------------------------------------------

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

This sure sounds like homework. Lots of people here on the board will be happy to help, but please show the queries you've tried that haven't worked and we can go from there.

"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

mm-1009269 SSCommitted Points: 1559 More actions · Answer 3

dreamslogic,

You'll want to use the 'count' function to find the total number.

select count(col.name)

-Michelle 🙂

Mike McQueen SSChasing Mays Points: 631 More actions · Answer 4

I agree that this looks a lot like homework, but I'll bite and at least give you a couple functions to look into.

Note that this is taking some guesses as to what yo actually need to be doing...

For B look into the SUM() function. You will want to run this over the entire table it looks like.

For C, you'll want to use the SUM function and then divide that by the COUNT of rows. Look into the COUNT function for this.

mm-1009269 SSCommitted Points: 1559 More actions · Answer 5

If you give a sample of the result set that would help too!

When doing your sum function make sure to check that you have the proper datatype... or else if will give you an error.

Like Mike said, search for sum() and count(). I think that will solve your issue.

-Michelle 🙂