Toy 2

PHOTO

Sat Jul 29 2023 01:11:22 GMT+0000 (Coordinated Universal Time)

Saved by @kentrickfelix #mysql

WITH CustomerSales AS (
	SELECT p.customerNumber, customerName, salesRepEmployeeNumber, SUM(amount) as total
	FROM payments p
	JOIN customers c ON p.customerNumber = c.customerNumber
	GROUP BY customerNumber
)
SELECT cs.salesRepEmployeeNumber, e.lastName, e.firstName, SUM(total) AS total
FROM CustomerSales cs
JOIN employees e ON cs.salesRepEmployeeNumber = e.employeeNumber
GROUP BY salesRepEmployeeNumber
ORDER BY total DESC
LIMIT 5;

Save snippets that work from anywhere online with our extensions

Available in the Chrome Web Store

Get Firefox Add-on

Get VS Code extension

Comments

More like this

mysql - What are these files ,can I delete them manually? - Server Fault

[root@jiaoyou mysql]# pwd
/var/lib/mysql
[root@jiaoyou mysql]# ls -ls
338256 -rw-rw---- 1 mysql mysql 346030080 2010-04-22 08:08 ibdata1
626812 -rw-rw---- 1 mysql mysql 641222072 2010-01-26 07:17 mysql-bin.000008
316892 -rw-rw---- 1 mysql mysql 324173772 2010-03-25 12:51 mysql-bin.000009
52724 -rw-rw---- 1 mysql mysql  53931666 2010-04-12 12:13 mysql-bin.000010
10136 -rw-rw---- 1 mysql mysql  10359639 2010-04-22 08:32 mysql-bin.000011

mysql> SHOW BINARY LOGS; 
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000008 | 641222072 | 
| mysql-bin.000009 | 324173772 | 
| mysql-bin.000010 |  53931666 | 
| mysql-bin.000011 |  10360680 | 
+------------------+-----------+


Solution

PURGE BINARY LOGS TO 'mysql-bin.010';

MySQL Database won't start in XAMPP Manager-osx - Stack Overflow

sudo killall mysqld
sudo /Applications/XAMPP/xamppfiles/bin/mysql.server start

MySQL set DB collation

ALTER DATABASE database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SQL iki tabloyu birleştirme

SELECT id, account_id, amount, created_at, null as type
 	FROM subscription_invoice
UNION ALL
SELECT id, account_id, amount, created_at, 
	`type` as type
FROM subscription_payment
ORDER BY created_at DESC

#prestashop #delete #mysql #db #database #ids

Delete from DB if not in array

Db::getInstance()->executeS("DELETE FROM product_images WHERE id_product NOT IN (" . join($allProductIds,',') . ")")

Create table sample

CREATE TABLE firends (
	id INTEGER,
  	firstname VARCHAR(64)
);

mySQL timestamp for created at

CURRENT_TIMESTAMP

mySQL timestamp for updated at

CURRENT_TIMESTAMP	ON UPDATE CURRENT_TIMESTAMP

Select Distinct column (a) & order by column (b)

SELECT ticker,`datetime`
FROM (
  SELECT ticker, MAX(`datetime`) `datetime`
  FROM Bloomberg_hist_data
  GROUP BY ticker
) t ORDER BY `datetime`

Update column with random string from list

UPDATE deltadb.transactions_temp
SET ticker =(CASE CEIL(RAND()*2)
              WHEN 1 THEN '3CN:SP'
              WHEN 2 THEN 'AGV:SP'
          END);

mySQL FIND IN SET Multiple

CONCAT(',', section_id, ',') REGEXP ',(val1|val2|val3),'"

SQL Database - School

-- these scripts will delete the tables if it's already there

DROP TABLE IF EXISTS StudentEnrollments;
DROP TABLE IF EXISTS Students;
DROP TABLE IF EXISTS Classrooms;


-- create and populate the students table
CREATE TABLE Students
(
	StudentId INTEGER PRIMARY KEY,
	FirstName VARCHAR(200) NOT NULL,
	LastName VARCHAR(200) NOT NULL,
	Nationality VARCHAR(100) NOT NULL,
	DateOfBirth DATETIME NULL
);


INSERT INTO Students
	(StudentId, FirstName, LastName, Nationality, DateOfBirth)
VALUES
	('1','Mickey', 'Mouse', 'American', '1991-05-02'),
	('2','Donald', 'Duck', 'Japanese', '1992-11-12'),
	('3','Goofy', 'Goof', 'American', '1980-04-15'),
	('4','Daisy', 'Duck', 'French', '1985-02-16'),
	('5','Huey', 'Duck', 'French', '1986-05-19'),
	('6','Scrooge', 'McDuck', 'Japanese', '1983-11-11'),
	('7','Minnie', 'Mouse', 'Canadian', '1983-11-30'),
	('8','Louie', 'Duck', 'French', '1985-09-09');

-- create and populate the classroom table
CREATE TABLE Classrooms
(
	ClassroomId INTEGER PRIMARY KEY,
	ClassName VARCHAR(200) NOT NULL,
	Weight DECIMAL NOT NULL
);

INSERT INTO Classrooms
	(ClassroomId, ClassName, Weight)
VALUES
	(1, 'Public Interaction', 0.10),
	(2, 'Pranks', 0.15),
	(3, 'Running', 0.15),
	(4, 'Acting', 0.30),
	(5, 'Making Jokes', 0.30);

-- create and populate the student enrollment table
CREATE TABLE StudentEnrollments
(
	StudentEnrollmentId INTEGER PRIMARY KEY,
	StudentId INTEGER NOT NULL,
	ClassroomId INTEGER NOT NULL,
	Grade DECIMAL NOT NULL,
	FOREIGN KEY(StudentId) REFERENCES Students(StudentId),
	FOREIGN KEY(ClassroomId) REFERENCES Classrooms(ClassroomId)
);

INSERT INTO StudentEnrollments
	(StudentEnrollmentId, StudentId, ClassroomId, Grade)
VALUES
	(1, 1, 1, 91),
	(2, 1, 2, 68),
	(3, 1, 3, 89),
	(4, 1, 4, 60),
	(5, 1, 5, 65),
	(6, 2, 1, 79),
	(7, 2, 2, 85),
	(8, 2, 3, 68),
	(9, 2, 4, 89),
	(10, 2, 5, 80),
	(11, 3, 1, 96),
	(12, 3, 2, 62),
	(13, 3, 3, 78),
	(14, 3, 4, 100),
	(15, 3, 5, 64),
	(16, 4, 1, 81),
	(17, 4, 2, 90),
	(18, 4, 3, 85),
	(19, 4, 4, 95),
	(20, 4, 5, 64),
	(21, 5, 1, 81),
	(22, 5, 2, 73),
	(23, 5, 3, 60),
	(24, 5, 4, 99),
	(25, 5, 5, 70),
	(26, 6, 1, 75),
	(27, 6, 2, 74),
	(28, 6, 3, 69),
	(29, 6, 4, 79),
	(30, 6, 5, 88),
	(31, 7, 1, 60),
	(32, 7, 2, 75),
	(33, 7, 3, 82),
	(34, 7, 4, 66),
	(35, 7, 5, 65),
	(36, 8, 1, 69),
	(37, 8, 2, 81),
	(38, 8, 3, 100),
	(39, 8, 4, 63),
	(40, 8, 5, 62);

template for SP

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_no_math_la$$
CREATE PROCEDURE sp_no_math_la()

BEGIN

...


END $$
DELIMITER ;

CALL sp_no_math_la();

IFNULL(tppp.preferredname, 'WA') AS 'SOR'
FROM person p
JOIN student_program sp ON sp.personid=p.personid AND sp.statusid=1 AND sp.is_deleted=0
JOIN enrollment e ON e.personid=p.personid
JOIN enrollmenttostudentdebit esd USING (enrollmentid)
JOIN studentdebit sd USING (studentdebitid)
LEFT JOIN studentdebit asd ON asd.adjustsdebitid=sd.studentdebitid AND asd.obsolete=0
LEFT JOIN thirdpartypayerengagement tppe ON tppe.tppengagementid=sd.tppengagementid
LEFT JOIN thirdpartypayer tpp ON tpp.tppayerid=tppe.tppayerid
LEFT JOIN person tppp ON tppp.personid=tpp.tppayerpersonid

SELECT ea.externalresponse, ea.recipientpersonid, ea.externalid, ea.customfield01, cc.crmid, concat (cc.firstname, ' ', cc.lastname) FROM externalactivity ea join crmcache cc on cc.crmid = ea.RecipientPersonID WHERE SuccessDate IS NULL

Browse more snippets >>