Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

SQL statement help

Here is my table structure

TABLEA

InvoiceID    OrderID    Amount
1                   1               $25
2                   2               $5
3                   2               $15
4                   3               $10
5                   2               $20
6                   4               $15
7                   4               $20


I need to select 1 row for each OrderID adding the amounts if there are multiplee OrderIDs

So when I select OrderID "1", I should get

InvoiceID    OrderID     Amount
1                  1                $25

If, however I select OrderID "2", I should get

InvoiceID     OrderID      Amount
2                   2                  $40

So far I have Select InvoiceID, OrderID, SUM(Amount) As Amount from Billing where OrderID = @OrderID Group by InvoiceID, OrderID Order by OrderID

that does not combine the results into 1 row. It has a separate row for each OrderID, but I need only 1 row for each OrderID.

Anyone know how to do this?

thank you
0
mlg101
Asked:
mlg101
  • 21
  • 10
  • 7
  • +2
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
select OrderID , sum(Amount) total
from <table>
-- insert WHERE here.
group by orderID
0
 
BuggyCoderCommented:
you cannot get invoiceid and orderid in your result because if we do so, we need to group the results by both invoiceid and orderid.

so the query
select orderid, sum(amount) from TableA
group by orderid
order by orderid

will generate
orderId    TotalAmount

as output
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
I guess why is invoiceID 2 and not 3 for your second example?



If, however I select OrderID "2", I should get

InvoiceID     OrderID      Amount
2                   2                  $40

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mlg101Author Commented:
Let me give you my sql query in its exactness so you can see, because I already have a group by clause. I thought it would be easier, so sorry about that...

"SELECT ClientFileNumber, CaseNumber, Plaintiff, Defendant, BillingType, ClientID, OrderID, BillClient, InvoiceDate, Paid, SUM(Amount) as Amount, SUM(AmountPaid) as AmountPaid FROM Billing WHERE ClientID = @ClientID Group by ClientFileNumber, CaseNumber, Plaintiff, Defendant, BillingType, ClientID, OrderID, BillClient, InvoiceDate, Paid Order by InvoiceDate"

As you can see, I already have "Group by" OrderID. but i have to have all other other variables in the Group by clause because i get an error otherwise.

does this make it more clear?
0
 
mlg101Author Commented:
@ged325, I'm not sure why invoice 2 is used and not invoice 3. InvoiceID doesn't really matter in my application, because invoiceID is not used at all, it is just in the database as primary key
0
 
BuggyCoderCommented:
Try This:-

select distinct ClientFileNumber, CaseNumber, Plaintiff, Defendant, BillingType, ClientID, OrderID, BillClient, InvoiceDate, Paid, tt.orderid,tt.amount from Billing a inner join (select orderid, sum(amount) from TableA group by orderid)tt on a.orderid=tt.orderid
where a.Clientid=@Clientid
hopefully everything else is constant and only orderid varies in your table...
0
 
mlg101Author Commented:
what do you mean by "constant" and "varies"? Because there can be duplicates of some of the other data too, but I will try your idea and see if it works
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
If you don't need invoice ID, don't take it.  The rest should flow through fine then.
0
 
mlg101Author Commented:
It says invalid object name "TableA"
0
 
BuggyCoderCommented:
use Billing :-

select distinct ClientFileNumber, CaseNumber, Plaintiff, Defendant, BillingType, ClientID, OrderID, BillClient, InvoiceDate, Paid, tt.orderid,tt.amount from Billing a inner join (select orderid, sum(amount) from Billing group by orderid)tt on a.orderid=tt.orderid
where a.Clientid=@Clientid
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
You have to replace tableA with your actual table name.

And make sure you're using the right database.
0
 
mlg101Author Commented:
ok i used billing, now i get this error

No column name was specified for column 2 of 'tt'.
Ambiguous column name 'OrderID'.
Invalid column name 'Amount'.
0
 
BuggyCoderCommented:
sorry about that, i am not sitting on my development desk so these glitches are happening...
My Fault:-(

select distinct ClientFileNumber, CaseNumber, Plaintiff, Defendant, BillingType, ClientID, OrderID, BillClient, InvoiceDate, Paid, tt.orderid,tt.amount from Billing a inner join (select orderid, sum(amount) as amount from Billing group by orderid)tt on a.orderid=tt.orderid
where a.Clientid=@Clientid
0
 
mlg101Author Commented:
Ok, I got it to render now, but I still get multiple columns.

here is the code i have now


"SELECT DISTINCT AffiliateFileNumber, CaseNumber, Plaintiff, Defendant, BillingType, ClientID, BillClient, InvoiceDate, Paid, tt.OrderID, tt.Amount, tt.amountpaid FROM Billing a inner join (select OrderID, sum(amount) As Amount, sum(amountPaid) As AmountPaid From billing group by OrderID)tt On a.OrderID= tt.OrderID WHERE a.ClientID = @ClientID"
0
 
BuggyCoderCommented:
This means for one order id there are multiple values of one or more of these:-
AffiliateFileNumber, CaseNumber, Plaintiff, Defendant, BillingType, ClientID, BillClient, InvoiceDate, Paid

try removing the un-necessary duplicate value columns....
Like if there are multiple invoice dates against one order id take it into the inner query and apply max over it like:-

SELECT DISTINCT AffiliateFileNumber, CaseNumber, Plaintiff, Defendant, BillingType, ClientID, BillClient, Paid, tt.InvoiceDate, tt.OrderID, tt.Amount, tt.amountpaid FROM Billing a inner join (select OrderID, max(InvoiceDate) as InvoiceDate, sum(amount) As Amount, sum(amountPaid) As AmountPaid From billing group by OrderID)tt On a.OrderID= tt.OrderID WHERE a.ClientID = @ClientID

you have to make the changes accordingly.
0
 
mlg101Author Commented:
what if there is a possibility for all of them to have duplicates? should I take them all over to the inner query? And if I do that, will it hurt it if by chance there is not a duplicate on one or more of them?
0
 
BuggyCoderCommented:
if they are all duplicates, re-look at your database design. may be its time for normalization......
0
 
howtodoitCommented:
This is your query

select ta.orderid, presum.totalamount
from tablea ta left join 
(select orderid,SUM(amount) as totalamount from tablea group by orderid) presum on presum.orderid=ta.orderid

Open in new window

0
 
mlg101Author Commented:
I tried moving them all over  to the inner query and got this:

Operand data type bit is invalid for max operator.
No column name was specified for column 2 of 'tt'.
No column name was specified for column 3 of 'tt'.
No column name was specified for column 4 of 'tt'.
No column name was specified for column 5 of 'tt'.
No column name was specified for column 6 of 'tt'.
No column name was specified for column 7 of 'tt'.
No column name was specified for column 8 of 'tt'.
No column name was specified for column 9 of 'tt'.
No column name was specified for column 10 of 'tt'.
Invalid column name 'AffiliateFileNumber'.
Invalid column name 'CaseNumber'.
Invalid column name 'Plaintiff'.
Invalid column name 'Defendant'.
Invalid column name 'BillingType'.
Invalid column name 'ClientID'.
Invalid column name 'BillClient'.
Invalid column name 'InvoiceDate'.
Invalid column name 'Paid'.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Try the below query...It get rids of duplicates before grouping and summing up...

Select BB.InvoiceID, BB.OrderID, SUM(BB.Amount) As Amount 
from (
          select distinct  InvoiceID, OrderID, Amount
          from Billing
          ) BB
Group by BB.InvoiceID, BB.OrderID 
having BB.OrderID = @OrderID

Open in new window

0
 
mlg101Author Commented:
@racimo I will try that when I get back in.

@codebuggy each entry in database is a billing item. Some items get billed more than once so that is why there are duplicates. How do you normalize that?
0
 
mlg101Author Commented:
@Racimo, it says you must declare the scalar variable @OrderID. Not sure why you have @OrderID there...
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
@OrderID would be the parameter you ought to pass...See below...

Change the Run this if you want to get grouped items for a single order...
declare @OrderID int

select @OrderID = 1 --> Parameter..

Select BB.InvoiceID, BB.OrderID, SUM(BB.Amount) As Amount 
from (
          select distinct  InvoiceID, OrderID, Amount
          from Billing
          where OrderID=@OrderID
          ) BB
Group by BB.InvoiceID, BB.OrderID 

Open in new window


Run this if you want to get grouped item for a range of orders
declare @OrderIDStart int,  @OrderIDEnd int

select @OrderStart = 1, --> Parameter1..
@OrderEnd = 10

Select BB.InvoiceID, BB.OrderID, SUM(BB.Amount) As Amount 
from (
          select distinct  InvoiceID, OrderID, Amount
          from Billing
          where OrderID between @OrderStart and @OrderEnd
          ) BB
Group by BB.InvoiceID, BB.OrderID 

Open in new window


Run this if you don't need any parameters at all...
Select BB.InvoiceID, BB.OrderID, SUM(BB.Amount) As Amount 
from (
          select distinct  InvoiceID, OrderID, Amount
          from Billing
          ) BB
Group by BB.InvoiceID, BB.OrderID 

Open in new window


Hope this helps...
0
 
BuggyCoderCommented:
billing Payment details have to be separate table with Referential integrity implemented between the two tables.

I Think what you want to achieve can only be done by writing a Routine that iterates over your table and fetches the data according to your requirement.

I don't see a query fulfilling your needs.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Now if you need to group by Client  you can run the below...

declare @OrderID int, @ClientID int

select @ClientID= 2, --> Replace 2 by NULL if you need grouped items for all clients..
@OrderID = 1, Replace 1 by NULL if you need grouped items for all orders..

Select BB.ClientID, BB.InvoiceID, BB.OrderID, SUM(BB.Amount) As Amount, MAX(InvoiceDate) as InvoiceDate
from (
          select distinct  ClientID, InvoiceID, OrderID, Amount, InvoiceDate
          from Billing
          where ClientID=isnull(@ClientID, ClientID) and
          OrderID=isnull(@OrderID, OrderID)
          ) BB
Group by BB.ClientID, BB.InvoiceID, BB.OrderID 
                                      

Open in new window

0
 
mlg101Author Commented:
@BuggyCoder, do you mean a table for jobs billed out and another table for payments received?


@Racimo, I have this which gets the error "incorrect syntax near the keyword 'where'"



"SELECT BB.ORDERID, SUM(BB.Amount) As Amount, Sum(BB.AmountPaid) As AmountPaid FROM (SELECT distinct LinkID, Amount, AmountPaid From Billing) BB Group by BB.ORDERID WHERE ClientID = @ClientID"
0
 
mlg101Author Commented:
I mean this:


"SELECT BB.ORDERID, SUM(BB.Amount) As Amount, Sum(BB.AmountPaid) As AmountPaid FROM (SELECT distinct ORDERID, Amount, AmountPaid From Billing) BB Group by BB.ORDERID WHERE ClientID = @ClientID"

Its says incorrect syntax near keyword "where"
0
 
mlg101Author Commented:
I just have to flip flop the where and group by clauses and it works, but it still doesn't work as i need it to work
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Randomly Flip-flopping your code or desired output expectations only makes it harder for us to help you and won't get you closer to what you need.

You initially asked for query that would allow you to transform the following BILLING table

InvoiceID    OrderID    Amount
1                   1               $25
2                   2               $5
3                   2               $15
4                   3               $10
5                   2               $20
6                   4               $15
7                   4               $20

into

InvoiceID    OrderID    Amount
1                   1               $25
2                   2               $40
4                   3               $10
6                   4               $35

if that is the case, the below query should help you...

Select MIN(BB.InvoiceID), BB.OrderID, SUM(BB.Amount) As Amount 
from (
          select distinct  InvoiceID, OrderID, Amount
          from Billing
          where OrderID= <REPLACE THIS BY SOME ORDERID VALUE>
          ) BB
Group by BB.OrderID 

Open in new window


If not, please claim clearly the expected output.  Give an example.
0
 
mlg101Author Commented:
First of all, I did not Randomly flip flop anything. You cannot put a where clause after a group by clause, it has to be the other way around, so your code was wrong and I had to figure out how to fix it before I could move on.

Secondly, you didn't look at my first comment, because I clearly stated there was more to the problem than my initial posting. At least BuggyCoder realized that and was trying to help me move forward from there.

If this is the kind of help I'm getting now days from Experts-Exchange, I'm going to cancel my subscription, because I now have 3 opened and unanswered questions on here.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<You cannot put a where clause after a group by clause, it has to be the other way around, so your code was wrong and I had to figure out how to fix it before I could move on.>>
Exactly, what portion of code did I put a WHERE condition after a GROUP BY ?  Can you post the code that I posted and that you claim you fixed.  It is perfectly possible to put a WHERE clause in an subquery that is grouped at a higher level.  Did you at least try the code I posted ?  or did you try fixing it without trying it ?

<<Secondly, you didn't look at my first comment, because I clearly stated there was more to the problem than my initial posting. >>
And what is the problem you mentioned ?  Can you describe it or should we start looking at our crystal balls right away ?

<<If this is the kind of help I'm getting now days from Experts-Exchange, I'm going to cancel my subscription, because I now have 3 opened and unanswered questions on here.>>
Well, I am sorry you feel unhelped but I invite you to read the EE guidelines 3 rules for Asking Questions:

http://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=13

Among other things, please understand that the quality of the input determines the quality of the answer you will receive and the fact that you do not get the answer you expected does not make the answer wrong.

Please bear in mind that EE is simply a community of skillfull professionals who benevolently help their peers when they can.  If you feel this does not do it for you, I am sure you will get better assistance from a dedicated support (but it will certainly cost you more.).  Besides, threatening of canceling your subscription will have little influence on experts responses.

Regards...
0
 
mlg101Author Commented:
you are right about the where and group by. I read it wrong, so sorry about that.

But if you read my first comment, it has my entire sql query I'm trying to make work, which has more variables than my original post. You were still trying to work out variables from my original post, which wasn't helping.

And i was not making a threat, just stating a fact. I know that no one would care if I cancelled, kind of like AT & T could care less if I use their cell phone service. I was just stating a fact, maybe I should have kept it to myself.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<You were still trying to work out variables from my original post, which wasn't helping.>>
Then perhaps you could consider reposting your complete question under the initial clearer format you opened the question with.  Bear in mind that as threads go longer, it may be difficult to retrace the need.

<<I know that no one would care if I cancelled, kind of like AT & T could care less if I use their cell phone service. >>
ATT may not care but you still accept they charge you a monthly fee.  EE is different and we (experts) do care since we freely respond with no obligation to do so.  If I did not care, I would not even answer your question and simply move on.  After all, you need help, not me.  

<<I was just stating a fact, maybe I should have kept it to myself.>>
Do as you wish.  Just stating facts too.  Just bear in mind we are simply trying to help you.

But since you keep ignoring the questions we are asking, you should not complain about not getting best feedback.  

So for the last time, please post a sample of the desired output under the format:

OUTPUT
InvoiceID    OrderID    Amount
1                   1               $25
2                   2               $5
3                   2               $15
4                   3               $10
5                   2               $20
6                   4               $15
7                   4               $20

Regards...
0
 
mlg101Author Commented:
Billing Table
InvoiceID  OwnerID, OrderID, AffiliateFileNumber, CaseNumber, Plaintiff, Defendant, BillingType, ClientID, BillClient, InvoiceDate, Paid, Amount, Amount Paid

1             1              1                 xyz                            xyz                 xyz           zyz                       xyz               123         True          3/20/12        True   $25       $5
2             1              2                 xyz                            xyz                 xyz           xyz                      xyz               123         True          3/20/12        true    $30      $10
3             1              2                 xyz                            xyz                  xyz          xyz                 xyz               123         True           3/20/12        True   $15      $10



output desired

OrderID, AffiliateFileNumber, CaseNumber, Plaintiff, Defendant, BillingType, ClientID, InvoiceDate, Paid, Amount, Amount Paid
1              xyz                              xyz                  xyz        xyz            xyz                123             
3/20/12         True    $25           $5
2              xyz                              xyz                  xyz        xyz            xyz                 123             3/20/12         True     $45           $20

Open in new window

0
 
mlg101Author Commented:
i couldnt get it to format right
0
 
mlg101Author Commented:
InvoiceID  OwnerID, OrderID, AffiliateFileNumber, CaseNumber, Plaintiff, Defendant, BillingType, ClientID, BillClient, InvoiceDate, Paid, Amount, AmountPaid

1             1       1         xyz                xyz           xyz       zyz       xyz           123         True     3/20/12     True   $25       $5
2             1       2         xyz                xyz           xyz       xyz       xyz           123         True     3/20/12     true   $30      $10
3             1       2         xyz                xyz           xyz       xyz       xyz           123         True     3/20/12     True   $15      $10



output desired

OrderID, AffiliateFileNumber, CaseNumber, Plaintiff, Defendant, BillingType, ClientID, InvoiceDate, Paid, Amount, AmountPaid
1          xyz                 xyz         xyz        xyz        xyz           123      3/20/12     True   $25        $5
2          xyz                 xyz         xyz        xyz        xyz           123      3/20/12     True   $45        $20

Open in new window

0
 
mlg101Author Commented:
ok the last one was right format. If you will notice that I want ORDERID to be combined into 1 row on the output, and the sum of the AMOUNT and AMOUNTPAID colums be displayed. The other fields, it does not matter which row it returns
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
That is much better thanks.  Try the below query and provide feedback...

select distinct 
	AAA.OrderID, 
	B.AffiliateFileNumber, 
	B.CaseNumber, 
	B.Plaintiff, 
	B.Defendant, 
	B.BillingType, 
	B.ClientID, 
	AAA.InvoiceDate, 
	B.Paid, 
	AAA.Amount, 
	AAA.AmountPaid
from Billing B
inner join
(
	select BLG.OrderID, 
	max(BLG.InvoiceDate) as InvoiceDate, 
	sum(BLG.Amount) as Amount, 
	sum(BLG.AmountPaid) as AmountPaid
	from
	(
	select distinct InvoiceID,  
		OwnerID, 
		OrderID, 
		AffiliateFileNumber, 
		CaseNumber, Plaintiff, 
		Defendant, 
		BillingType, 
		ClientID, 
		BillClient, 	
		InvoiceDate, 
		Paid, 
		Amount, 
		AmountPaid
		from Billing
	) BLG
	group by BLG.OrderID
) AAA
on B.OrderID = AAA.OrderID and B.InvoiceDate = AAA.InvoiceDate
order by B.OrderID

Open in new window

0
 
mlg101Author Commented:
Using what you last gave me, I get this error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Also, I have a questions, in my query, I have a Where clause, that takes into consideration variables on the page, such as:

Where ClientID = @ClientID
Parameters.Add("@ClientID", SqlDbType.Int).Value = Request.QueryString("Company")

So I need to put the Where ClientID = @ClientID in the SQL statement in the right place.

thank you
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Sorry...try this...

select distinct top 100 percent
	AAA.OrderID, 
	B.AffiliateFileNumber, 
	B.CaseNumber, 
	B.Plaintiff, 
	B.Defendant, 
	B.BillingType, 
	B.ClientID, 
	AAA.InvoiceDate, 
	B.Paid, 
	AAA.Amount, 
	AAA.AmountPaid
from Billing B
inner join
(
	select BLG.OrderID, 
	max(BLG.InvoiceDate) as InvoiceDate, 
	sum(BLG.Amount) as Amount, 
	sum(BLG.AmountPaid) as AmountPaid
	from
	(
	select distinct InvoiceID,  
		OwnerID, 
		OrderID, 
		AffiliateFileNumber, 
		CaseNumber, Plaintiff, 
		Defendant, 
		BillingType, 
		ClientID, 
		BillClient, 	
		InvoiceDate, 
		Paid, 
		Amount, 
		AmountPaid
		from Billing
	) BLG
	group by BLG.OrderID
) AAA
on B.OrderID = AAA.OrderID and B.InvoiceDate = AAA.InvoiceDate
order by AAA.OrderID

Open in new window

0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<So I need to put the Where ClientID = @ClientID in the SQL statement in the right place.>>

This should help...

declare @ClientID int
select @ClientID = --<SOME VALUE YOU PASS FROM YOUR VB CODE> if the value is NULL the query would return row for all clients....

select distinct top 100 percent
	AAA.OrderID, 
	B.AffiliateFileNumber, 
	B.CaseNumber, 
	B.Plaintiff, 
	B.Defendant, 
	B.BillingType, 
	B.ClientID, 
	AAA.InvoiceDate, 
	B.Paid, 
	AAA.Amount, 
	AAA.AmountPaid
from Billing B
inner join
(
	select BLG.OrderID, 
	max(BLG.InvoiceDate) as InvoiceDate, 
	sum(BLG.Amount) as Amount, 
	sum(BLG.AmountPaid) as AmountPaid
	from
	(
	select distinct InvoiceID,  
		OwnerID, 
		OrderID, 
		AffiliateFileNumber, 
		CaseNumber, Plaintiff, 
		Defendant, 
		BillingType, 
		ClientID, 
		BillClient, 	
		InvoiceDate, 
		Paid, 
		Amount, 
		AmountPaid
		from Billing
	) BLG
	group by BLG.OrderID
) AAA
on B.OrderID = AAA.OrderID and B.InvoiceDate = AAA.InvoiceDate
where B.ClientID=isnull(@ClientID, ClientID)
order by AAA.OrderID

Open in new window

0
 
mlg101Author Commented:
Thank you for working with me even when I was frustrated. You know what you are doing and I appreciate your help. This worked perfectly, after adding my  "where ClientID = @ClientID", because I already had the parameter built in.

Thanks again.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Glad you could solve your issue...

And keep in mind: we are not AT&T :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 21
  • 10
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now