Sql Query Compare and Sum

Question

I have these problem I need to match the sum a columns to see if they match with the Final Total of the Invoice by Invoice Number ( I am working in a query to do it) Example

Invoice No      Line _no      Total Line  Invoice total   Field I will create
----------------------------------------------------------------------
45                 1            145            300              145
45                 2            165            300              300    Match

46                 1             200           200               200   Match  

47                 1             100           300               100
47                 2             100           300               200 
47                 3             100           300               300   Match

Do you just need the grand total of each invoice to match the invoice total or do you need to show your work? if show your work, you want to use over syntax sum of total_line over invoice No. Running total [Example](http://stackoverflow.com/questions/10450425/sql-running-total-grouped-by-date) — xQbert, Sep 08 '14 at 18:00
I need a create a new column and see if the total of the invoice are matching when you sum the lines and see if all the invoice match — Luis64, Sep 08 '14 at 18:02

Gordon Linoff · Answer 1 · 2014-09-08T20:35:19.550

4

You want a cumulative sum. In SQL Server 2012+, just do:

select e.*,
       (case when InvoiceTotal = sum(InvoiceTotal) over (partition by invoice_no order by line_no)
             then 'Match'
        end)
from example e;

In earlier versions of SQL Server, I would be inclined to do it with a correlated subquery:

select e.*
       (case when InvoiceTotal = (select sum(InvoiceTotal) 
                                  from example e2
                                  where e2.Invoice_no = e.invoice_no and
                                        e2.line_no >= e.line_no
                                 )
             then 'Match'
        end)
from example e;

You can also do this with a cross apply as M Ali suggests.

EDIT:

Now that I think about the problem, you don't need a cumulative sum. That was just how I originally thought of the problem. So, this will work in SQL Server 2008:

select e.*,
       (case when InvoiceTotal = sum(InvoiceTotal) over (partition by invoice_no)
             then 'Match'
        end)
from example e;

You can't get the cumulative sum out (the second to last column) without more manipulation, but the match column is not hard.

edited Sep 08 '14 at 20:35

answered Sep 08 '14 at 18:02

Gordon Linoff

1,242,037
58
646
786

Your 1st solution seems very sleek but it throws an error could please fix it, I would love to learn this syntax :) [`SQL FIDDLE`](http://sqlfiddle.com/#!3/653ee/7), Since I dont know how to do it I cant find the error. – M.Ali Sep 08 '14 at 18:23
@M.Ali You didn't select SQL Server 2012. – Martin Smith Sep 08 '14 at 19:53
@MartinSmith SUM() Over Partition by .... I thought it was supported in 2008 isnt it ? – M.Ali Sep 08 '14 at 19:54
1

@M.Ali the `order by` to do running totals isn't. – Martin Smith Sep 08 '14 at 19:56
Cheers @MartinSmith I havent worked much with 2012 hence didnt know this. I thought I was missing something as error message doesnt say `Not supported function used` it just says `error near order by`. Thanks for advice though. – M.Ali Sep 08 '14 at 20:01

M.Ali · Answer 2 · 2014-09-08T18:16:14.697

2

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE TEST(InvoiceNo INT, Line_no INT, TotalLine INT, InvoiceTotal INT)

INSERT INTO TEST VALUES 
(45 ,1 ,145 ,300),
(45 ,2 ,165 ,300),
(46 ,1 ,200 ,200),
(47 ,1 ,100 ,300),
(47 ,2 ,100 ,300),
(47 ,3 ,100 ,300)

Query 1:

SELECT  t.[InvoiceNo]
       ,t.[Line_no]
       ,t.[TotalLine]
       ,t.[InvoiceTotal]
       ,C.Grand_Total
       ,CASE WHEN C.Grand_Total = t.[InvoiceTotal] 
               THEN 'Match' ELSE '' END AS [Matched]
FROM TEST t
      CROSS APPLY (SELECT SUM([TotalLine]) AS Grand_Total
                   FROM TEST
                   WHERE [InvoiceNo] = t.[InvoiceNo]
                    AND  [Line_no] < = t.[Line_no]) C

Results:

| INVOICENO | LINE_NO | TOTALLINE | INVOICETOTAL | GRAND_TOTAL | MATCHED |
|-----------|---------|-----------|--------------|-------------|---------|
|        45 |       1 |       145 |          300 |         145 |         |
|        45 |       2 |       165 |          300 |         310 |         |
|        46 |       1 |       200 |          200 |         200 |   Match |
|        47 |       1 |       100 |          300 |         100 |         |
|        47 |       2 |       100 |          300 |         200 |         |
|        47 |       3 |       100 |          300 |         300 |   Match |

edited Sep 08 '14 at 18:16

answered Sep 08 '14 at 18:02

M.Ali

67,945
13
101
127

+1 . . . Although I think the comparison on line numbers is wrong (and I would definitely use table aliases for the subquery), the logic seems basically correct. – Gordon Linoff Sep 08 '14 at 18:07
I actually had downvoted. Your first revision of the answer didn't actually gave the column that op wanted, so – Lamak Sep 08 '14 at 18:14
2

And, just as a reference for the future, if you post an incomplete answer for the sake of being the first, then don't be surprised if you get downvotes that later **aren't** corrected (like this time) when you decide to update your answer – Lamak Sep 08 '14 at 18:33
Also Mr @Lamak For future reference, before you down vote someone there a section below answers section called "Comments Section", Hold your horses and leave a comment before you decide to down vote someone. specially if it is only a column missing from answer. – M.Ali Sep 08 '14 at 18:37
3

I don't need to do that. On the other hand, if you are gonna call downvoters "clowns" then you do need to post a complete answer, otherwise you are just whining – Lamak Sep 08 '14 at 18:43
1

@M.Ali as always you seem to be under the impression that downvotes are somehow taboo. If I don't like your answer I get to downvote. That's how downvoting works. It's not up to me to post a comment and wait to see if you change your answer. If you don't want to get Downvoted then put up a complete answer. People can downvote you for any reason they choose. – Zane Sep 08 '14 at 18:51
If an answer is WRONG, it should be down voted but down voting an answer just because YOU THINK its not what it should have been, seems pretty reckless, Good answers should be appreciated and bad WRONG answers should be down voted, not a rocket science at all my friend. Down voting just for the sake of down voting is petty, winds you up :) – M.Ali Sep 08 '14 at 18:58
If I put up a wrong answer or a poor quality answer there are people much moreeeeeeeee experienced and knowledgeable then Lamak would ever be, they leave suggestion to improve answers, Aaron Bretrand and Gordon Linoff are two prime examples. – M.Ali Sep 08 '14 at 19:00
@M.Ali And to think that all this time I have avoided calling you names or anything. I guess that this is what I get for giving you advice in posting incomplete answers and for deciding to always check if an answer that I downvote is corrected so I can un-downvote (as I did here). *sigh* – Lamak Sep 08 '14 at 19:03
1

@M.Ali a couple of words of advice: (1) don't be in such a rush to post answers. This is why they're often incomplete, guesses, or otherwise worthy of down-votes. (2) don't take down-votes so personally. They're about the content, not about you. (3) don't start attacking people personally. If you fix (1) and (2) you won't ever have to get to (3). – Aaron Bertrand Sep 08 '14 at 19:04
3

@M.Ali When you hover over the down arrow for voting it says _This answer is not useful_, if your first version failed to include the column that the OP wanted then it would be perfectly acceptable to downvote it. An answer doesn't have to be wrong to receive a downvote. – Taryn Sep 08 '14 at 19:05
Oh, and for what it's worth, probably 9 times out of 10 I *don't* leave comments about why I've down-voted. Often because other people have already explained why, but the rest of the time, precisely to avoid (3). – Aaron Bertrand Sep 08 '14 at 19:05
1

@Lamak Im sorry I called you name, everything aside I kinda lost it, I really am sorry pal, just a bit hot headed today. :( I am feeling bad now :S – M.Ali Sep 08 '14 at 19:06
Cheers AaronBertrand and bluefeet thanks for your advices, and @Lamak I apologize matey. – M.Ali Sep 08 '14 at 19:10
@M.Ali It's ok, don't worry about it. – Lamak Sep 08 '14 at 19:21

score 1 · Accepted Answer · answered Sep 08 '14 at 18:02

Is this what you're looking for? I think subquery is what you're asking about, but i'm guessing to get an end result similar to the entire thing.

select t."Invoice No", t."Line no_", t."Invoice total", 
        calcTotals.lineNum as calcSum, case when t."Invoice total" = calcTotals.lineNum then 'matched' else 'not matched' end
from [table] t
inner join (
    select "Invoice No" as invoiceNumber,
        sum("Line _no") as lineNum
    from [table]
    group by "Invoice No"
) calcTotals on t."Invoice No" = calcTotals.invoiceNumber

Sql Query Compare and Sum

3 Answers3