Can we use aggregate functions within group by?

These are duplicates, you must manually provide guarantee that there is no duplicate data in the destination database, that is, it must be empty.

but how?( –overwrite-tables didn’t help (

Help, please, to understand what is going on:(
I’m using docker image percona:5.7
Have the next problem, I’m dumping db’s correctly:

#!/bin/bash

for db in mysql aff_profits_test
do
time
mydumper
–host=127.0.0.1
–user=root
–password=pass
–outputdir=/home/ec2-user/full/$db
–rows=10000
-G -E -R
–compress
–build-empty-files
–threads=1
–verbose=3
–compress-protocol
–database=$db
done
Dumping proccess was successful and without any errors

Then I tried to restore mysql and aff_profits_test db’s for example:

#!/bin/bash

for db in mysql aff_profits_test
do
time
myloader
–host=127.0.0.1
–user=root
–password=pass
–directory=/home/ec2-user/full/$db
–threads=1
–verbose=3
–queries-per-transaction=100
–compress-protocol
–overwrite-tables
–database=$db
done
During restoring, I’m getting some errors:

** Message: 16:30:03.145: Thread 1 restoring `mysql`.`columns_priv` part 0
** Message: 16:30:03.145: Thread 1 restoring `mysql`.`db` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`event` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`func` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`ndb_binlog_index` part 0
** Message: 16:30:03.146: Thread 1 restoring `mysql`.`proc` part 0

** (myloader:22186): CRITICAL **: 16:30:03.151: Error restoring mysql.proc from file mysql.proc.sql.gz: Table ‘mysql.proc’ doesn’t exist
Creating table `mysql`.`plugin`
** Message: 16:30:02.994: Dropping table or view (if exists) `mysql`.`proc`
** Message: 16:30:02.995: Creating table `mysql`.`proc`

** (myloader:22186): CRITICAL **: 16:30:02.996: Error restoring mysql.proc from file mysql.proc-schema.sql.gz: Invalid default value for ‘modified’

When restoring my own db, get the next errors:

Error restoring aff_profits_test.default_rate_shave_history from file aff_profits_test.default_rate_shave_history.sql.gz: Duplicate entry ‘1’ for key ‘PRIMARY’
** Message: 12:50:08.363: Thread 1 restoring `aff_profits_test`.`department_countries` part 0
** Message: 12:50:08.363: Thread 1 restoring `aff_profits_test`.`department_rate_tables` part 0
** Message: 12:50:08.363: Thread 1 restoring `aff_profits_test`.`department_rates` part 0
** Message: 12:50:08.364: Thread 1 restoring `aff_profits_test`.`department_site_alias` part 0
** Message: 12:50:08.364: Thread 1 restoring `aff_profits_test`.`department_sites` part 0
** Message: 12:50:08.364: Thread 1 restoring `aff_profits_test`.`details` part 0

* (myloader:3844): CRITICAL *: 12:50:08.426: Error restoring aff_profits_test.details from file aff_profits_test.details.sql.gz: Duplicate entry ’59eef12677075′ for key ‘PRIMARY’
** Message: 12:50:08.426: Thread 1 restoring `aff_profits_test`.`emails` part 0

* (myloader:3844): CRITICAL *: 12:50:08.447: Error restoring aff_profits_test.emails from file aff_profits_test.emails.sql.gz: Duplicate entry ‘1’ for key ‘PRIMARY’
** Message: 12:50:08.447: Thread 1 restoring `aff_profits_test`.`emails_parts` part 0
** Message: 12:50:08.447: Thread 1 restoring `aff_profits_test`.`errors` part 0
** Message: 12:50:08.447: Thread 1 restoring `aff_profits_test`.`faq_items` part 0

Team, question, Does PREPARE and EXECUTE triggers implicit commit?

Select salary from table_name group by Salary having salary = max(salary);

ps: Select salary from table_name group by dept having salary = max(salary);

Is this right?

no

ps: I wrote it wrong. Is this correct ->Select salary from table_name group by dept having salary = max(salary);
Kindly point out why, Too.

I think cuz having clause can only be used to filter out the group…so in this case max(salary) is an aggregate function which works on whole the group and we can’t extract salary of a single row alone… So max(salary) can only be compared with something else like..>10000 etc

ok sorry, i wrote it wrong, is this right then Select salary from table_name group by dept having salary = max(salary);

Why don’t you run this query and see the result?

I want to know the reason behind not working.

Oh man I have written the reason….when using group by clause you can only use column as a whole in having clause . a single row can’t be used….like u have used salary

Like when u r using salary=max(salary)…u wanna compare each salary column (or cell) with max(salary)

yeah. So salary here means each particular row salary within a group. Am I wrong and the max(salary) means maximum within each group.

Ya

ps: I wrote it wrong. Is this correct ->Select salary from table_name group by dept having salary = max(salary);
Kindly point out why, Too.

suppose I’ve a table with columns: Emp_id, E_name, Dept, Salary

and i want to find the maximum salary branch wise.

ps: I wrote it wrong. Is this correct ->Select salary from table_name group by dept having salary = max(salary);
Kindly point out why, Too.

I think cuz having clause can only be used to filter out the group…so in this case max(salary) is an aggregate function which works on whole the group and we can’t extract salary of a single row alone… So max(salary) can only be compared with something else like..>10000 etc

I want to know the reason behind not working.

Didn’t get the meaning of using column as a whole. Can you elaborate this?