Results 1 to 4 of 4

Thread: [RESOLVED] SQL Query to get sum of same invoice numbers ...

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Resolved [RESOLVED] SQL Query to get sum of same invoice numbers ...

    Hi,

    How to write a SQL Query to get Sum of all invoices with same numbers? Using the following code:

    Code:
    "Select * from SalesTable  ORDER BY InvoiceNumber ASC"
    The attached image shows what actually my requirement is.

    Table contains the following data :
    InvoiceNo InvoiceDate CustomerName ModelNumber Rate Quantity Total Amount CGSTPercent CGSTAmount SGSTPercent SGSTAmount TotalWithTax
    101 11/20/2017 SUMIT SHOE MART P5632 200.00 10 2,000.00 2.5% 50.00 2.5% 50.00 2,100.00
    101 11/20/2017 SUMIT SHOE MART P4205 300.00 10 3,000.00 2.5% 75.00 2.5% 75.00 3,150.00
    101 11/20/2017 SUMIT SHOE MART P3200 400.00 20 8,000.00 2.5% 200.00 2.5% 200.00 8,400.00
    102 11/22/2017 RAMES SHOE PALACE P3600 350.00 10 3,500.00 2.5% 87.50 2.5% 87.50 3,675.00
    102 11/22/2017 RAMES SHOE PALACE P1505 275.00 5 1,375.00 2.5% 34.38 2.5% 34.38 1,443.75

    Result shall be like this :
    InvoiceNo InvoiceDate CustomerName Quantity Total Amount CGSTPercent CGSTAmount SGSTPercent SGSTAmount TotalWithTax
    101 11/20/2017 SUMIT SHOE MART 40 13,000.00 2.5% 325.00 2.5% 325.00 13,650.00
    102 11/22/2017 RAMES SHOE PALACE 15 4,875.00 2.5% 121.88 2.5% 121.88 5,118.75
    Attached Images Attached Images  
    Last edited by VS2013; Nov 28th, 2017 at 02:45 PM.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: SQL Query to get sum of same invoice numbers ...

    you select the SUM() of what ever field(s) you want to sum up.... and GROUP BY the rest of the fields...
    so if I have a table with NAME, MEAL, TOTAL and I want to see the sum of everything I bought: select NAME, sum(TOTAL) as TOTAL from MyTABLE group by NAME
    If I want to see the sum of all the different things, broken out by those things: select NAME, MEAL, sum(TOTAL) as TOTAL from MyTABLE group by NAME, MEAL

    What you're looking for is an AGGREGATE function (sum, min, max, etc)...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,298

    Re: SQL Query to get sum of same invoice numbers ...

    Questions on how to write SQL queries have nothing to do with VB.NET. I've asked the mods to move this thread to the Database Development forum.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Re: SQL Query to get sum of same invoice numbers ...

    Quote Originally Posted by techgnome View Post
    you select the SUM() of what ever field(s) you want to sum up.... and GROUP BY the rest of the fields...
    so if I have a table with NAME, MEAL, TOTAL and I want to see the sum of everything I bought: select NAME, sum(TOTAL) as TOTAL from MyTABLE group by NAME
    If I want to see the sum of all the different things, broken out by those things: select NAME, MEAL, sum(TOTAL) as TOTAL from MyTABLE group by NAME, MEAL

    What you're looking for is an AGGREGATE function (sum, min, max, etc)...

    -tg
    Problem resolved. Thanks for your kind support.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width