logo
down
shadow

Mysql single select with dynamic columns using max


Mysql single select with dynamic columns using max

By : Tapan Jain
Date : November 21 2020, 11:01 PM
it fixes the issue I found the solution....by adding GROUP BY it works and all rows are returned correctly!
code :


Share : facebook icon twitter icon
Select dynamic Columns in mysql

Select dynamic Columns in mysql


By : Hossein Lotfi
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , Unfortunately MySQL does not have a PIVOT function which is basically what you are trying to do. So you will need to use an aggregate function with a CASE statement:
code :
SELECT type,
  sum(case when criteria_id = 'env' then result end) env,
  sum(case when criteria_id = 'gas' then result end) gas,
  sum(case when criteria_id = 'age' then result end) age
FROM results
group by type
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(CRITERIA_ID = ''',
      CRITERIA_ID,
      ''', RESULT, NULL)) AS ',
      CRITERIA_ID
    )
  ) INTO @sql
FROM
  Results;
SET @sql = CONCAT('SELECT type, ', @sql, ' FROM Results GROUP BY type');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
How to update multiple columns with a single select query in MySQL?

How to update multiple columns with a single select query in MySQL?


By : Nathan
Date : March 29 2020, 07:55 AM
should help you out Let take a example, there are two tables as bellow.
code :
UPDATE OldData o, NewData n 
SET n.name = o.name, n.address = o.address 
where n.nid=234 and o.id=123;
Pandas select rows where columns are dynamic and a single column's value is greater than zero

Pandas select rows where columns are dynamic and a single column's value is greater than zero


By : Anurag Mundra
Date : March 29 2020, 07:55 AM
With these it helps Check the entire DataFrame for the condition and sum across rows. If that equals 1, the condition holds:
code :
two.loc[(two>0).sum(axis=1)==1]
Out: 
   one  two
a   10  0.0


three.loc[(three>0).sum(axis=1)==1]
Out: 
   blue  red  two
a    10    0    0
three.loc[lambda x: (x>0).sum(axis=1)==1]
Out: 
   blue  red  two
a    10    0    0
mysql Select values from multiple columns into single column

mysql Select values from multiple columns into single column


By : user1978906
Date : March 29 2020, 07:55 AM
To fix the issue you can do You could just use two queries and use the union statement of the two to append the two sets:
code :
Select id, v1 as v
From table 
where v1 is not null

union all

select id, v2 as v
from table
where v2 is not null
Mysql Select multiple columns and group by single column

Mysql Select multiple columns and group by single column


By : user2046997
Date : March 29 2020, 07:55 AM
I wish this help you In the Derived table, you are doing Group By on uid. Instead, you need to Group By on action_key to get the maximum value of createddate for every action_key.
Now, you can join this result-set to the main table on action_key and createddate.
code :
SELECT uos.uid,
       uos.value,
       uos.action_key
FROM   (SELECT action_key,
               Max(createddate) AS maxcreateddate
        FROM   usersoptstatus 
        WHERE uid = 1607 
        GROUP  BY action_key) A
INNER JOIN usersoptstatus AS uos
  ON uos.action_key = A.action_key AND 
     uos.createddate = A.maxcreateddate
WHERE  uos.uid = 1607
ORDER  BY uos.id DESC; 
Related Posts Related Posts :
  • Querying related rows based on order and column value
  • Can't decide whether normalization or de-normalization would work
  • Laravel working out with multiple data in a column
  • MYSQL - Order By Id In DESC Order, Group By X
  • MySQL Foreign Key Values
  • mysql union two queries with order by
  • I have three tables. I want to have one table. ReDesign my table
  • mySQL query Randomly takes a long time to execute
  • mysql Sorting by Event Name
  • Sum multiple identical tables in mySQL
  • SQL Enum Error 1064
  • Does MySQL maintain a time complexity of O(logn) when searching for a range of values?
  • SQL : 2 columns in 1 in result set
  • mySQL, select BOTH table_rows AND column_name from information_schema
  • I can't get max from counted value in sql
  • MySQL Interval Issue Extra Results
  • Select with inner join, start and Limit SQL
  • efficient way to match users common data in the database?
  • How to select all values of 'A' that match with several B's (MySQL)
  • A Problem Storing Arabic Chars in Database
  • Cannot create .edmx for MySql Database with mysql.data.entity.EF6 in Visual Studio
  • Single batched query or mapped queries for efficiency?
  • Concurrent mysql queries causing large query queue's
  • How to create two tables with first 75% and remaining 25% of another table in MySQL?
  • How to understand this MySQL query containing a JOIN?
  • MySQL/Rails - Mysql2::Error (Access denied for user 'rails_user'@'localhost' to database 'simple_cms_development'):
  • Atomic counter - redis vs postgres or other?
  • SELECT in WHERE clause that uses outer information
  • Obtaining differences between MySQL tables
  • insert into 2column table, avoiding duplicate records
  • How do I create a stored procedure that accepts a user Id and returns the user’s full name and occupation and 1 email ad
  • Replace subquery with join
  • Can a left join be expressed as a right join and vice versa?
  • Is mysql designed to create and drop databases frequently?
  • MySQL: How do I check which entries for each group has a value greater than the average values in its corresponding grou
  • Ruby MySQL Database Migration Script
  • Complex meta_query and orderby with multiple custom post types, and multiple date column names
  • Update table in SQL, WHERE clause multiple fields
  • How to check a condition whether table exist or not in RMysql
  • MySQL GROUP unless value equals 0
  • MySQL SELECT WHERE only digits, spaces and one slash
  • How to use foreign key in this case?
  • Mysql - use result of another query inside subquery
  • How to write a SQL query that can get below result?
  • Receiving SQLNonTransientConnectionException on Google Appengine
  • getting common joins for every combination of entries from a table
  • MySQL: Add a condition on a subquery from a joint table
  • mysql stored procedure with the if condition gives errors
  • Related products approach suggestion, MySQL InnoDB/PHP
  • SQL count rows where id = x and other max(id) grouped on the same table
  • Select time no people in area - mysql
  • mysql extract month from date format
  • How to select records based on 2 dates in mysql
  • MySQL parametrized query with like operator
  • How to make MySQL use less memory?
  • I'm using a MySQL table to log user data (1000 new rows / min). How can I rotate the rows after 2 days?
  • Fetch Game with time of EST Timezone
  • How do I approach queries like this in MySQL, Group_Concat maybe?
  • Sort values in two column and insert order position into another column in mysql
  • quote in csv file to import into MySQL
  • shadow
    Privacy Policy - Terms - Contact Us © soohba.com