Solarpitch Posted January 9, 2009 Share Posted January 9, 2009 Hey, I cant get my head around the logic of this query. Below is an example of the table I have, called "cash_off". Its for a POS application. There's 3 things I need to do with the table.. 1: Calculate the amount of POS terminals there are. (below there are 2) Will need to know this so for step 2 so it can check how many reports there are for each POS. 2: Print out the time and date of each record for a POS eg: POS 1 - Cashed Off at 1:25am, 9 Jan - Cashed Off at 1:30am, 9 Jan POS 2 - Cashed off at 1:30am, 9 Jan 3: Print out the Total for each POS eg: POS1 Total - €293.90 POS2 Total - €788.50 Table Example... ID POS Total Time Date 1 1 225.20 1:25am 9 Jan 2 1 68.70 1:30am 9 Jan 3 2 788.50 1:30am 9 Jan I'm not too sure how to approach this to be honest. The part thats confusing me is step 1 Quote Link to comment Share on other sites More sharing options...
gevans Posted January 9, 2009 Share Posted January 9, 2009 OK, this is just off the top of my head, but will be along the right lines. SELECT `POS`, `Total`, `Time`, `Date` FROM `table_name` GROUP BY `POS` Try that in PhpMyAdmin (or your equivalent) and see what table you get back, then we can see how it's looking Quote Link to comment Share on other sites More sharing options...
Solarpitch Posted January 9, 2009 Author Share Posted January 9, 2009 Hey, I just ran that in phpmyadmin and got... ID POS Total Time Date 1 1 225.20 1:25am 9 Jan Which is the first result in the db. Quote Link to comment Share on other sites More sharing options...
gevans Posted January 9, 2009 Share Posted January 9, 2009 are you sure you ran my exact code? you would not have got ID and would have got more than one result if your table is populated as you stated Quote Link to comment Share on other sites More sharing options...
Solarpitch Posted January 9, 2009 Author Share Posted January 9, 2009 sorry my mistake. I had to put a second POS in the table, I removed it when I was testing. I got something similar to this... POS Total Time Date 1 225.20 1:25am 9 Jan 2 788.50 1:25am 9 Jan doesnt seem to include the second cash off from POS1 Quote Link to comment Share on other sites More sharing options...
gevans Posted January 9, 2009 Share Posted January 9, 2009 I have a feeling this will either requre to queries, or a bit more manipulation before you display the data.... Quote Link to comment Share on other sites More sharing options...
Solarpitch Posted January 9, 2009 Author Share Posted January 9, 2009 Yeah, I was thinking it would take 2 queries alright. The part I'm having the difficulty is calculating the number of POS terminals, then getting the reports for them. For example if I run this query... SELECT POS, count( POS )FROM cash_off WHERE date = '.$date.' AND path = "real" GROUP BY `POS` This will return... POS count(till) 1 2 2 1 Which is saying thier are 2 POS terminal, POS1 has 2 cash off's for that date and POS2 has 1 cash off. It's what to do next with this info is where I'm stuck Quote Link to comment Share on other sites More sharing options...
gevans Posted January 9, 2009 Share Posted January 9, 2009 SELECT id, count( POS ) FROM cash_off WHERE date = '.$date.' AND path = "real" GROUP BY `id` Quote Link to comment Share on other sites More sharing options...
Solarpitch Posted January 9, 2009 Author Share Posted January 9, 2009 That just returns... ID count(POS) 1 1 2 1 3 1 Quote Link to comment Share on other sites More sharing options...
gevans Posted January 9, 2009 Share Posted January 9, 2009 EDIT SELECT id, count( POS ) FROM cash_off WHERE date = '.$date.' AND path = "real" GROUP BY `POS` Quote Link to comment Share on other sites More sharing options...
Solarpitch Posted January 9, 2009 Author Share Posted January 9, 2009 Umm ... I have an idea... do you know how to sum the value of multiple columns? For example SELECT SUM( bartotaltrue ) AS bt, SUM( foodtotaltrue ) AS ft, SUM( specialtotaltrue ) AS st FROM golfpro_bar_control_zxread WHERE date = '20081209' AND path = 'real' This will return.. bt ft st 896.20 221.23 652.30 Do you know how I can add these then? I might have a way round this. Quote Link to comment Share on other sites More sharing options...
Solarpitch Posted January 9, 2009 Author Share Posted January 9, 2009 Actually I figured my last post out...I'll see if I can sort my first issue with it now. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.