Geek Answers Handbook

Amount in left SQL join

I have two tables that I want to join, let's say table a and table b. Table b has many rows for table a, table b contains prices (actually a shopping cart). So what I want is all the entries from table a and the sum of the prices from table b. I tried

select a.*, sum(b.ach_sell) from bookings a left join pricing_line b on b.bookings = a.id

However, this is obviously not the way I wish, it ends with the total of all ach_sell (therefore, one record is returned). Will someone kindly offer me a solution that will help? I'm doing it programmatically right now, and I'm sure it can be done in SQL?

+4
source share
5 answers

Your right, just add a group by clause to separate a.id, something like this:

 select a.id, sum(b.ach_sell) from bookings a left join pricing_line b on b.bookings = a.id group by a.id
+10
source

Group by fields from the reservation table:

 select a.this, a.that, sum(b.ach_sell) from bookings a left join pricing_line b on b.bookings = a.id group by a.this, a.that
+2
source

You need GROUP BY something if you don't need only one result. Maybe?

 select a.*, sum(b.ach_sell) as ach_sell from bookings a left join pricing_line b on b.bookings = a.id GROUP BY a.id

Other RDBMSs will insist that you have the entire list of columns in GROUP BY , but MySQL does not.

0
source 
 select a.*, sum(b.ach_sell) as sum_column from bookings a left join pricing_line b on b.bookings = a.id group by a.id

Each time you use the aggregation functions ( SUM , COUNT , MIN , MAX , AVG ), you need to group by some other sets of fields (typically an identifier) ​​that you don’t use the aggregation function.

0
source

If you have many columns in the list.

Like this:

 SELECT co.checkOutOrderID, com.companyCode, sd.serviceDispatchCode, cu.customerName, com.companySimp, ISNULL(SUM (sdlp.partsModelPrice * sdlp.partsModelNum), 0) AS total FROM checkoutorder co LEFT JOIN servicedispatchorder sd ON sd.serviceDispatchID = co.serviceDispatchID LEFT JOIN customer cu ON cu.customerID = co.customerID LEFT JOIN company com ON com.companyID = co.companyID LEFT JOIN servicedispatchlinkparts sdlp ON sdlp.serviceDispatchID = sd.serviceDispatchID GROUP BY sd.serviceDispatchID, co.checkOutOrderID, com.companyCode, sd.serviceDispatchCode, cu.customerName, com.companySimp
0
source

More articles:


All Articles