Post reply

SQL Query

  • 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

  • September 17, 2009 at 3:13 am

    #1054847

    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.

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

  • September 17, 2009 at 6:46 am

    #1054937

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • September 17, 2009 at 8:41 am

    #1055020

    dreamslogic,

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

    select count(col.name)

    -Michelle 🙂

  • September 17, 2009 at 11:39 am

    #1055159

    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.

  • September 17, 2009 at 11:47 am

    #1055167

    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 🙂

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

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