Search the Community

Hi people, I'm having a problem with my SQL query which is not doing what I would like. I have this query: SELECT a. id, a.kataloski_broj, SUM(a.quantity) AS zbroj, entry_type_desc, posting_date, item_category, product_group, b.nabavna_cijena as neto_nabavna, b.neto_VPC, b.preporucena_VPC, c.nabavna_cijena as rabat_nabavna, c.VPC, d.VPC as NAV_VPC, d.PVPC as NAV_PVPC FROM prodaja_zaliha a LEFT JOIN kalkulacija_stavke b ON a.kataloski_broj = b.kataloski_broj LEFT JOIN kalkulacija_stavke_rabat c ON a.kataloski_broj = c.kataloski_broj LEFT JOIN katalog_pribora_item d ON a.kataloski_broj = d.kataloski_broj_NAV WHERE a.entry_type_desc = 'Prodaja' AND YEAR(a.posting_date) = '2015' AND a.kataloski_broj = 'M1200' GROUP BY a.id ASC The problem is that it returns SUM(a.quantity) AS zbroj in 1432 quantity, while there is actually 182 quantity in DB. What am I doing wrong?

Hi, I am trying to add values together in my while loop, so far I have: $q->query($DB, $SQL); while($avrating=$q->getrow()): $averageratings = $avrating['sum(rating)'] / $noofreviews; $overallaverage = round($averageratings, 2) + round($averageratings, 2); echo '<p>'.$avrating['rating_name'].': '.round($averageratings, 2).'</p>'; endwhile; echo 'x'.$overallaverage; $overallaverage += $overallaverage / 10; echo 'The Average: '.$overallaverage; from this, I get: x7.34The Average: 8.074 the total shold be: 42.34 then dived by 10 should be 4.23

Hello. I'm trying to get this piece of code finished but it's not going my way, if anyone could help me out that would be great. Here whats I'm trying to do: Table1 Table2 ---------------- --------------------------------- ID | | PlayerID | PlayerName | ---------------- -------------------------------- 1393 | | 1393 | Player1 | 2097 | | 2097 | Player2 | 3888 | | 3888 | Player3 | 3888 | | 4017 | Player4 | 3888 | --------------------------------- 4017 | 4017 | 4017 | ---------------- I Want to Count the entries in Table1 (3888 has 3 entries so it will display like "3888: 3") Then I want to join Table1 and Table2 using the ID so I can get the players name (3888=Player3 so it would display like Player3 : 3) Here's the code I'm using: <?php //connect to db $query = "SELECT * FROM Table1 INNER JOIN Table2 WHERE Table1.PlayerID = Table2.ID"; $query2 = "SELECT ID, SUM(ID=0) AS n0, SUM(ID=1) AS n1, COUNT(*) AS total FROM Table1 GROUP BY ID"; $result = mysql_query($query) or die($query."<br/><br/>".mysql_error()); $result2 = mysql_query($query2) or die($query2."<br/><br/>".mysql_error()); echo "<table border='1'> <tr> <th>PlayerName</th> <th>Entries</th> </tr>"; while($row2 = mysql_fetch_array($result2)) while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['ID'] . "</td>"; echo "<td>" . $row['PlayerName'] . "</td>"; echo "<td>" . $row2['Total'] . "</td>"; echo "</tr>"; } echo "</table>"; mysql_close($link); ?> If I use $query on its own it joins perfectly, If I use $query2 on its own, it displays the results exactly how I want them listed but with the ID instead of the PlayerName, I tried putting them both together as shown above but I can't get them to work together. What I want my end Result to be Player1 1 Player2 1 Player3 3 Player4 3 How it keeps coming out; 1393 1 2097 1 3888 3 4017 3 Can anyone see where I'm going wrong? Thank you!!

I'm making statistic for 5 tables. I have made the example with one client data. loan: payment_schedule: payment_schedule_row payment_schedule_cover: payment_schedlue_delay: And the query is: SELECT period, loan_sum, covers, delay FROM (SELECT MAX(EXTRACT(YEAR_MONTH FROM psc.date)) AS period, (SELECT SUM(psr2.payment) FROM payment_schedule_row AS psr2 WHERE psr.payment_schedule_id = psr2.payment_schedule_id) AS loan_sum, (SELECT SUM(psc2.sum) FROM payment_schedule_cover AS psc2 WHERE psc.payment_schedule_id = psc2.payment_schedule_id) AS covers, (SELECT SUM(psd2.delay) FROM payment_schedule_delay AS psd2 WHERE psr.id = psd2.payment_schedule_row_id) AS delay FROM loan INNER JOIN payment_schedule AS ps ON ps.loan_id = loan.id INNER JOIN payment_schedule_row AS psr ON psr.payment_schedule_id = ps.id INNER JOIN payment_schedule_cover AS psc ON psc.payment_schedule_id = ps.id WHERE loan.status = 'payed' GROUP BY ps.id) AS sum_by_id GROUP BY period Sqlfiddle link: http://sqlfiddle.com/#!2/21585/2/0 Result for the query: period | loan_sum | covers | delay --------------------------------------- 201407 | 384 | 422 | 0.07 Everything is right except the delay. It should be 0.11 (0.07 + 0.03 + 0.01) So I have been trying to find the error from the query for days now. Maybe someone can tell me what I'm doing wrong.

Heey, Could some one help me to make like 5 / 10 sum generator with random codes and each sum on a other page? Maybe you got skype and you could help me out and help me? So I got teached also I was thinking about to make a IF - statement to make a If page <= then page 10 he will make random sums and at the end show the result? But I don't know how I need to make that. got this page so far: <?php SESSION_START(); if(!empty($_POST['submit'])) { $pre=$_SESSION['ans'']; $answer=$_POST['ant']; $1= rand(0,30); $2= rand(0,30); $answer=$1+$2; $_SESSION['ans']=$answer; echo "<br>$1+$2="; } else { ?> <html> <head> <title>Math</title> </head> <body> <h2>+</h2> <form method='post' action=''> Your answer:<input name='ans'><br> </select> <br><br> <input name='submit' type='submit' value='next'> <input name='reset' type='reset' value='delete'> </form> <?php } ?> Thank you~

hi i need help on this, i have three input fields in html form lets say: input 1 = 10 input 2 = 3 input 3 = 13 (sum) so whenever i change values of input 1 or input 2 it will still compute for the sum.. so if: input 1 = 4 input 2 = 3 input 3 = 7 (sum) im using "onchange" method here.. thanks in advance.

I have the following query that gives me the result i want. In a nutshell it searches two columns to find the max value in each of these two columns and returns the sum of these two values. When i try to pass it to a variable in php it doesn't seem to work. I want to be able to pass the sum in a variable so i can use the variable later into an IF Condition. For example: if ($scoremax >= '160') { code here } Here is my query: select (max(scoreioa) + max(scoreioa2)) from result where username='username' and here is the php code: $maxscore = mysql_query("select (max(scoreioa) + max(scoreioa2)) from result where username='username'"); mysql_data_seek($maxscore, $scoremax); $row = mysql_fetch_row($maxscore); $scoremax = $row[0];

I want to add the number with this code while($i>0) { $j=$i%10; $i=$i/10; $sum=$sum+$j; } I have number in $i=12345, now after execution, sum must return the sum of all digits of its number 15 Example: $i=23, sum=5 $i=18, sum=9 $i=19, sum=10 As you can see the code must terminate as soon as $i==0, but I am facing infinite loop........thanks

I have 2 tables, one named donations and the other named costs. Both have a common column, Amount, I want to Sum each column then take costs away from donations. Is this possible? Thanks for any help. Robbie

Hi everyone, I'm trying to give my students points for doing various things on my website. Eg: Completing a quiz, getting a certain score, all result in points, which are thrown into a table called points. Now I'm trying to extract a leaderboard for the table. I'm halfway there, and have a list of scores ranked from highest to lowest, next to the member's id. See code below: $query = "SELECT point_member_id, SUM(points) FROM tbl_points GROUP BY point_member_id ORDER BY SUM(points) DESC"; $result = mysql_query($query) or die(mysql_error()); // Print out result while($row = mysql_fetch_array($result)){ echo $row['point_member_id']. " : ". $row['SUM(points)']; echo "<br />"; } However, I really don't want point_member_id displaying. I'd like the student's firstname and lastname displaying. firstname and lastname are the fields 'member_firstname' and 'member_lastname' in the table 'members'. So what I need to do is extract this information where members.member_id = points.point_member_id I've tried a bunch of variations to the query and searched ad nauseum, but nothing is helping. Any advice would be greatly appreciated. Cheers, Dave

A little background on the project I'm working on: I have a reasonably large number of values, over 500k rows worth. The values are generated per hour, per parent. I want to group these values by one of four time intervals: Month, week, day or hour, and get the average for this period. The challenge comes by the fact that I need to split the values into two different blocks, based upon whether the value was generated during the "day" or "night/weekend". So far I've come up with two different approaches to this: Have two fields in the table for saving the values, one for the "daytime" values and the other for "night time/weekend" values. This would allow me to run separate functions on the fields, without involving the date functions in MySQL. At the cost of having two fields for what is essentially the same type of data, and having to do the splitting in the pre-insert phase. Save all values in the same field, and then use MySQL's datetime functions with a CASE-WHEN to figure out in which block they belong. This approach seems to be the cleanest one in terms of database-design, but I fear it will put a lot of strain on the database. Especially since the SELECT statements will run more often than the insertion script. So the question is if I should go for the first approach, the second, or if there is some other solution to this that I've failed to grasp? I could really do with some expert help on this one. Example values: Desired output:

I'm creating a system for compiling orders for my business. The script basically is supposed to compile a summarised list of items to order from our various suppliers. I currently have a script that allows me to add items from a list of products via search of drop down menu to a current list of products that need to be ordered. What I'm trying to do now is make is so that if the same item is added to the order twice, it only takes up one row in the table and therefore in the list and adds the quantities together. My current attempt at the script looks like this... <?php $con = mysql_connect("localhost","username","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("database name", $con); $count='1'; $TotalQuantity= '$count + tCurrentOrder(Quantity)'; $sql="INSERT INTO tCurrentOrder (Item, Quantity, Date) VALUES ('$_POST[item]','$count','now()') ON DUPLICATE KEY UPDATE tCurrentOrder (Quantity) VALUES ('$TotalQuantity')"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo ("Item Added To Order. <a href=\"http page address">here</a> to continue."); $url = 'http page address'; echo '<META HTTP-EQUIV=Refresh CONTENT="0; URL='.$url.'">'; mysql_close($con); ?> The $totalquantity seems to be the current problem. What should it be? Thank you in advance.

Having issue trying to sum list/menu selected values to show on a textbox <!DOCTYPE html> <html> <head> <script type="text/javascript" src="js/jquery.js"></script> <script type="text/javascript" src="js/jquery-1.4.2.js"></script> <style> p { color:red; margin:4px; } b { color:blue; } </style> </head> <body> <p><input name="wal" id="wal" type="text"></p> <p><input type="text" id="total" value="0"></p> <select size="7" multiple="multiple" id="multiple"> <option value="1" selected="selected">Item 1</option> <option value="2">Item 2</option> <option value="3">Item 3</option> <option value="4">Item 4</option> </select> <script> function displayVals() { var multipleValues = $("#multiple").val() || []; $("#wal").val( multipleValues.join("+ ")); var total =($('#wal').val()); // Update the total var postid=total.split("+"); var sum = 0; //iterate through each textboxes and add the values $("#wal").each(function() { //add only if the value is number if(!isNaN(this.value) && this.value.length!=0) { sum += parseFloat(this.value); } }); //.toFixed() method will roundoff the final sum to 2 decimal places $("#total").html(sum.toFixed(2)); } $("select").change(displayVals); displayVals(); </script> </body> </html> Can anyone help with this please

Heya I've been having a hard time getting my mysql query to do as I want. SELECT m.team, SUM(pc.points) AS total_points FROM members AS m JOIN pointscount AS pc ON pc.tournamentid = m.tournament JOIN persons AS p ON p.pid = m.person WHERE p.secret = 1 AND m.tournament = 5 AND pc.day < 8 GROUP BY m.team SELECT m.team, SUM(pc.points) AS total_points FROM members AS m JOIN pointscount AS pc ON pc.tournamentid = m.tournament JOIN persons AS p ON p.pid = m.person WHERE p.secret = 0 AND m.tournament = 5 GROUP BY m.team Yeah, this is what I've been writing. The table `members` contain information about what kind of tournament and team a certain person is in. The table `pointscount` contain information about how many points a person has got each day of a certain tournament, one row per person per day per tournament. So yes, they are grouped as being UNIQUE together. The table `persons` contains the id of a person, but it also contains a field called secret, which if is set to true is supposed to be used to keep the points they've obtained secret after a certain date. What I want the query to do: Get the team name and get the total of points this team has earned, but this with a little twist. I want the second query to only sum the amount of points earned by the team where it's members have had their secret field in the person table set to false. The first one wants to do almost the opposite, but instead of getting everyone who has set their secret field in the person table to true, it wants to only get the point they've earned before the 8th day. o.o' Hopefully this makes any sense. Thanks in advance.