logo
down
shadow

Get Max value, Distinct values from Table in MySQL


Get Max value, Distinct values from Table in MySQL

By : Wesley Chavez
Date : November 21 2020, 09:01 AM
I think the issue was by ths following , It is possible to get that all in 1 query, but it isn't good practice since that data doesn't represent anything. MySQL is a Relational DataBase Management System (RDBMS) so the data is output has to have a relation together.
In this case, you want the max(price), distinct(brands) and distinct(colors), with no relations between them and you should make 3 different calls
code :


Share : facebook icon twitter icon
PHP / MySQL - Create array of distinct values, query db table for data associated with those values, and loop for each

PHP / MySQL - Create array of distinct values, query db table for data associated with those values, and loop for each


By : tajdink
Date : March 29 2020, 07:55 AM
Any of those help I don't quite understand completely but I'm guessing you want to output all of the competitors and their shares but this is only outputting the last competitor? If so the way to fix that would be to put your echoes in your foreach loop instead of creating another while loop.
code :
<?php
$product = $_GET['product'];
$cat = $_GET['cat'];
$state = $_GET['state'];
$metric = $_GET['metric'];

$table = $product ."_specs_states";

$q = " SELECT distinct(competitor) as competitor from $table";
            $result = $dbc->query($q) or die("unable to execute the query<br />" . $dbc->errno . "<br />" . $dbc->error);
            $r = $result->fetch_array();
            $competitors = array();

            do {
                $competitors[] = $r[0];

      } while ($r = $result->fetch_array());
      echo $competitors;


    foreach($competitors as $competitor){


            $q = "SELECT * FROM $table where state = '$state' && bigcat = '$cat' && product = '$product' && metric = '$metric' && competitor = '$competitor'";
            $result = $dbc->query($q) or die("unable to execute the query<br />" . $dbc->errno . "<br />" . $dbc->error);

            $r = $result->fetch_array();

                $share1 = ($r[5]);
                $share2 = ($r[6]);
                $share3 = ($r[7]);
                $share4 = ($r[8]);
                $share5 = ($r[9]);
                $share6 = ($r[10]);
                $share7 = ($r[11]);
                $share8 = ($r[12]);
                $share9 = ($r[13]);
                $share10 = ($r[14]);
                $share11 = ($r[15]);
                $share12 = ($r[16]);

                echo $competitor ."<br />";
echo $share1 ."<br />";
echo $share2 ."<br />";
echo $share3 ."<br />";
echo $share4 ."<br />";
echo $share5 ."<br />";
echo $share6 ."<br />";
echo $share7 ."<br />";
echo $share8 ."<br />";
echo $share9 ."<br />";
echo $share10 ."<br />";
echo $share11 ."<br />";
echo $share12;

}//end loop

?>
MySQL selecting distinct values from a table, ordering by the count of rows with those distinct values

MySQL selecting distinct values from a table, ordering by the count of rows with those distinct values


By : user3242973
Date : March 29 2020, 07:55 AM
wish helps you I'm currently working on a MySQL query, but I've been having a bit of trouble with it and am looking for help. I have been struggling with it for a while now and have searched for the solution from the Internet and from different coding forums, but, alas, still nothing. , What you'll need is GROUP BY and COUNT.
code :
SELECT `value_1`, COUNT(`value_1`) AS `count` FROM `data` WHERE `value_2` != '0' GROUP BY `value_1` ORDER BY `count`;
MySQL - Taking DISTINCT values from table A and turning into PRIMARY KEY on table B

MySQL - Taking DISTINCT values from table A and turning into PRIMARY KEY on table B


By : Hopkins Trawacho
Date : March 29 2020, 07:55 AM
it should still fix some issue If you are only trying to insert new values, this should work. You could make a left join to the target table and only include records that don't exist (WHERE pbu.ProductID IS NULL).
code :
INSERT INTO ProductDescriptionsbackup(ProductID) 
SELECT DISTINCT p.`PRODUCT CODE` 
FROM   Products as p
       LEFT JOIN ProductDescriptionsbackup as pbu
       ON p.`PRODUCT CODE` = pbu.ProductID
WHERE  pbu.ProductID IS NULL;
MySQL query to select distinct values from one table and all values from a separate table

MySQL query to select distinct values from one table and all values from a separate table


By : Atul_101
Date : March 29 2020, 07:55 AM
I wish did fix the issue. You have't specified well enough about your result but as I could understand,You may try this hope it works
SELECT * FROM Table1
MySQL select Distinct records in 1 table and count each group based on values in another table

MySQL select Distinct records in 1 table and count each group based on values in another table


By : chathurika Sanjeewan
Date : March 29 2020, 07:55 AM
wish help you to fix your issue Table 1: , You can use SUM with a combination of BETWEEN and DISTINCT
code :
SELECT
SUM(DISTINCT `table2`.`ratio` BETWEEN 0.00 AND 0.25) `count`
FROM table1 JOIN table2 ON (table1.user =table2.user) GROUP BY table1.user

SELECT
SUM(DISTINCT `table2`.`ratio` BETWEEN 0.25 AND 0.50) `count`
FROM table1 JOIN table2 ON (table1.user =table2.user) GROUP BY table1.user

SELECT
SUM(DISTINCT `table2`.`ratio` BETWEEN 0.50 AND 0.75 ) `count`
FROM table1 JOIN table2 ON (table1.user =table2.user) GROUP BY table1.user

SELECT
SUM(DISTINCT `table2`.`ratio` BETWEEN 0.75 AND 0.99) `count`
FROM table1 JOIN table2 ON (table1.user =table2.user) GROUP BY table1.user
SELECT  '0.00 - 0.25' Ratio_Group ,
SUM(DISTINCT `table2`.`ratio` BETWEEN 0.00 AND 0.25) `count`
FROM table1 JOIN table2 ON (table1.user =table2.user) GROUP BY table1.user
UNION ALL
SELECT '0.25 - 0.50' Ratio_Group ,
SUM(DISTINCT `table2`.`ratio` BETWEEN 0.25 AND 0.50) `count`
FROM table1 JOIN table2 ON (table1.user =table2.user) GROUP BY table1.user
UNION ALL
SELECT '0.50 - 0.75' Ratio_Group ,
SUM(DISTINCT `table2`.`ratio` BETWEEN 0.50 AND 0.75 ) `count`
FROM table1 JOIN table2 ON (table1.user =table2.user) GROUP BY table1.user
UNION ALL
SELECT '0.75 - 0.99' Ratio_Group ,
SUM(DISTINCT `table2`.`ratio` BETWEEN 0.75 AND 0.99) `count`
FROM table1 JOIN table2 ON (table1.user =table2.user) GROUP BY table1.user
SELECT  '0.00 - 0.25' Ratio_Group ,
COUNT(DISTINCT `table2`.`user` ) `count`
FROM table1 JOIN table2 ON (table1.user =table2.user) 
WHERE `table2`.`ratio` BETWEEN 0.00 AND 0.25

UNION ALL

SELECT '0.25 - 0.50' Ratio_Group ,
COUNT(DISTINCT `table2`.`user`) `count`
FROM table1 JOIN table2 ON (table1.user =table2.user) 
WHERE  `table2`.`ratio` BETWEEN 0.25 AND 0.50

UNION ALL

SELECT '0.50 - 0.75' Ratio_Group ,
COUNT(DISTINCT `table2`.`user` ) `count`
FROM table1 JOIN table2 ON (table1.user =table2.user) 
WHERE `table2`.`ratio` BETWEEN 0.50 AND 0.75
UNION ALL

SELECT '0.75 - 0.99' Ratio_Group ,
COUNT(DISTINCT `table2`.`user`) `count`
FROM table1 JOIN table2 ON (table1.user =table2.user) 
WHERE `table2`.`ratio` BETWEEN 0.75 AND 0.99
Related Posts Related Posts :
  • SQL - Setting a maximum number when incrementing a value by 1
  • MySql - Join, Sum, and return the Sums as columns
  • How can I join a table on two fields and retrieve a single column from both matching results?
  • Ruby: Cleanup my code w/ stored procedure?
  • How to structure a MySQL database for counties, cities, and states
  • User-Defined Functions Returning Table in MySQL
  • How would I return the result of SQL math operations?
  • MySQL fetch parent and all children
  • Create a mysql database and use normalization
  • Ruby: Sequel gem Insert to Mysql
  • Tryton ERP MySQL installation
  • Show the most recently added row in a table for each distinct name
  • Rake task that purge just a single table
  • Amazon RDS MySQL support for JSON functions
  • cakephp join for selectbox
  • Column width of mysql output
  • SQL: Specifying a unique constraint through a foreign key and efficiently querying it
  • SQL add space in column string
  • MySQL select rows based on two tables
  • Dealing With The Type Blob within Scala's Case Classes
  • mysql limit and offset in large table
  • No MySQL driver selectable in JBoss new Datasource wizard
  • Drupal db_insert('node') promote to front page equel to 1 not working
  • How can we show records even if id is null?
  • How to retrieve the last 30 days records for a user from current date
  • How to stop a running procedure in MySQL?
  • How to merge the tailing fields in a sorted result in SQL?
  • Error (2014. Commands out of querys) with all complex querys on MySQL
  • Slow Query Execution joining multiple tables
  • Is there a way to add header to mysql query results?
  • creating CRUD inserting issue
  • sub queries error
  • SQL / MySQL: Sum all amounts for respective month
  • join 2 mysql select based on text field
  • Bluemix- Bind Mobilefirst Container to Cloud Foundry-App
  • joining two table and show the attribute name instead of id
  • How to correct the following query
  • Error while creating a table
  • How to export database through putty?
  • mysql : How to update or insert new rows DAILY?
  • Is there a way to retrieve the minimum value of a datatype in MySQL?
  • printing numbers 1 to 100 with 10 numbers per line
  • SQL music playlist database design
  • How can I count the users with more than 1 Order?
  • r - dbWriteTable or a MySQL Delete query?
  • org.hibernate.MappingException:Could not determine type for
  • Separating SQL Tables while still allowing connections
  • Mysql Sum Conditional
  • How can I add a column that increments on another column in same table?
  • Mysql statement to list tables of certain columns?
  • MYSQL Error at restoring database: Please DISCARD the tablespace before IMPORT
  • Merge/Combine two select statements into one
  • Add text string to existing mySQL table
  • SQL newbie query issue
  • MySQL Workbench 6.3 Can't Create Columns
  • MySQL server table creation - "invalid syntax"
  • What is DataDir in MySQL?
  • sql get couple of elements
  • How to access variable in subquery?
  • Mysql decimal(10,9) stop to 9.999999999
  • shadow
    Privacy Policy - Terms - Contact Us © animezone.co