Welcome to WebmasterWorld Guest from 54.167.0.111

Forum Moderators: open

Message Too Old, No Replies

MySQL SUM

     

deimon

7:11 pm on Jun 29, 2010 (gmt 0)

5+ Year Member



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

7:26 pm on Jun 29, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

8:08 pm on Jun 29, 2010 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



If isNull doesn't work try

coalesce(items.price,0) AS price

deimon

8:59 pm on Jun 29, 2010 (gmt 0)

5+ Year Member



why is there "SELECT totals.id" ? .. and double FORM is True ? or second is WHERE ?

LifeinAsia

9:13 pm on Jun 29, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



"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

12:36 am on Jun 30, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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 . . .
 

Featured Threads

Hot Threads This Week

Hot Threads This Month