homepage Welcome to WebmasterWorld Guest from 50.17.27.205
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved