| MySQL SUM
|
deimon
msg:4161548 | 7:11 pm on Jun 29, 2010 (gmt 0) |
Hello, i have 2 tables :
Bills: id.......
Items: id | price.....
.. its tables in my billing program, where i need show all bills ..
i wrote sql query as :
SELECT bills.id, SUM(items.price) FROM bills, items, WHERE bills.id = items.id
but they wrote only rows, where is some items ... bills where not any items not shows.
I need show all bills (where isnt any items show "0")
Have you some ideas ?
Thanks
|
LifeinAsia
msg:4161559 | 7:26 pm on Jun 29, 2010 (gmt 0) |
Off the top of my head, this should work (if you're using MS SQL):
SELECT totals.id, SUM(totals.price)
FROM (SELECT bills.id as id, IsNull(items.price,0) AS price
FROM bills LEFT OUTER JOIN items ON bills.id=items.id) AS totals
GROUP BY totals.id
If your DB doesn't have an IsNull function, you'll have to pay with it a bit.
|
Demaestro
msg:4161583 | 8:08 pm on Jun 29, 2010 (gmt 0) |
If isNull doesn't work try
coalesce(items.price,0) AS price
|
deimon
msg:4161615 | 8:59 pm on Jun 29, 2010 (gmt 0) |
why is there "SELECT totals.id" ? .. and double FORM is True ? or second is WHERE ?
|
LifeinAsia
msg:4161624 | 9:13 pm on Jun 29, 2010 (gmt 0) |
"totals" is the name of the aliased subquery (SELECT bills.id as id, IsNull(items.price,0) AS price
FROM bills LEFT OUTER JOIN items ON bills.id=items.id) that you can imagine as a table that you can use regular SELECTs on.
I don't understand your other questions.
|
rocknbil
msg:4161746 | 12:36 am on Jun 30, 2010 (gmt 0) |
The second "is where" is a nested query. If it's mySQL, a join will show rows whether matching join rows or not, so you **might** not need a test for null. The rows without joined rows will output as NULL column values, not sure how you turn that to 0 but there's probably a way.
You also need to use group by with sum.
| but they wrote only rows, where is some items ... bills where not any items not shows. |
|
In this example, note how I've left out items 1236-1238 from items, and added multiple entries for others to test that sum is working.
create table bills (rec_id int(11) primary key auto_increment, id int(11) not null default 0);
create table items (rec_id int(11) primary key auto_increment, id int(11) not null default 0, price decimal(12,2) not null default '0.00');
insert into bills (id) values (1234);
insert into bills (id) values (1235);
insert into bills (id) values (1236);
insert into bills (id) values (1237);
insert into bills (id) values (1238);
insert into bills (id) values (1239);
insert into bills (id) values (1240);
insert into items (id,price) values (1234,'125.00');
insert into items (id,price) values (1234,'50.00');
insert into items (id,price) values (1234,'18.00');
insert into items (id,price) values (1235,'125.00');
insert into items (id,price) values (1239,'125.00');
insert into items (id,price) values (1239,'300.00');
insert into items (id,price) values (1240,'125.00');
select bills.id, sum(items.price) as sums from bills left join items on bills.id = items.id group by bills.id;
results in
id | sums
1234| 193.00
1235| 125.00
1236| NULL
1237| NULL
1238| NULL
1239| 425.00
1240| 125.00
You'd have to evaluate the null value in your programming and output a 0, or maybe there's a way to print NULL as zero . . .
|
|
|
