TIPS
*** Connect to local databasel
-- comment 
-- Format format(fidTot,2)
	-- add $$ SELECT CONCAT('$', FORMAT(val, 2))
====================
Loop thru Totals
====================
SELECT fidId, CONCAT('$', FORMAT(fidTot, 2)) AS Total, aTot, mTot, nTot, bTot, cTot, dTot, eTot, fidComment, DATE_FORMAT(fidCurDate, '%m-%d-%Y %W') AS Date FROM `fidelity` ORDER BY fidId DESC LIMIT 12;
====================
Explain command to see indexes used
====================
EXPLAIN SELECT * FROM `fidelity` order by fidCurDate DESC;

====================
Export to Csv file
====================
mysqldump --no-data --skip-comments --host=localhost.com -u root --password='DrumHill1;' fidelity penguins > penguins.sql


====================
Monthly max min - need begin and end balance really
====================
set @mYear='2022';
SELECT Year(fidCurDate)as Year, MonthName(fidCurDate) as Month,
CONCAT('$',FORMAT(Max(FidTot), 0)) as MaxTotal,
CONCAT('$',FORMAT(MIN(FidTot), 0))  as MinTotal,
CONCAT('$',FORMAT(Max(fidTot)-MIN(FidTot), 0))  as MinTotal,

COUNT(fidId) as RecsFound, fidCurDate
FROM fidelity
where Year(fidCurDate) = @mYear
GROUP BY EXTRACT(YEAR_MONTH FROM fidCurDate);
====================
Min Max one Query
====================
(
    SELECT
        `fidId`,
        MAX(`fidTot`),
        `fidCurDate`
    FROM
        `fidelity`
    where Year(`fidCurDate`) = 2020
    order by `fidTot` DESC

)union
    (
    SELECT
        `fidId`,
        min(`fidTot`),
        `fidCurDate`
    FROM
        `fidelity`
        where Year(`fidCurDate`) = 2020
        order by `fidTot` DESC
)
                =========================
		NOT WORKING Two Queries
		========================
		SELECT
    '2021' AS YEAR,
    t1.minTot,
    t1.MinDate,
    t2.maxTot,
    t2.MaxDate
FROM
    (
    SELECT
        c.fidCurDate AS MaxDate,
        MAX(c.fidTot) AS maxTot
    FROM
        fidelity c
    WHERE
        YEAR(c.`fidCurDate`) = 2021
) AS t2,
(
    SELECT
        d.fidCurDate AS MinDate,
        MIN(d.fidTot) AS minTot
    FROM
        fidelity d
    WHERE
        YEAR(d.`fidCurDate`) = 2021
) AS t1
=================
Get max/min 2021
=================

SELECT fidId, fidTot, fidCurDate
  FROM fidelity
 WHERE fidTot =
(
  SELECT MAX(fidTot) MaxTot
    FROM fidelity where  Year(fidCurDate) = '2021'
)
============ 
get min/Max 
=============
select fidId, fidTot as minFidTot, fidCurDate from fidelity where year(fidCurdate) = '2021' order by fidTot asc  limit 1
select fidId, fidTot as maxFidTot, fidCurDate from fidelity where year(fidCurdate) = '2021' order by fidTot desc limit 1

============ 
min/max with sub query - can't do for one year
============ 
select date(fidCurDate), fidTot as 'min/max' from fidelity where year(fidCurDate) = '2022' and fidTot = (select max(fidTot) from fidelity) union all select date(fidCurDate), fidTot from fidelity where fidTot = (select min(fidTot) from fidelity)

============ 
 NonManaged vs Managed - with totals compared
============ 
$sql="SELECT fidId, date(fidCurDate) as 'Date',fidTot as 'Total', (ar091+ai163+at420) as 'Non-Managed' , (mi866+mi543+mt592+as551) as 'Managed', (ar091+ai163+at420 + mi866+mi543+mt592+as551) as newTot FROM `fidelity` WHERE year(fidCurDate) = '2022' order by fidCurDate desc;";


WEEKLY RECORDS
SELECT fidId, fidCurDate, Year(fidCurDate), week(fidCurDate) FROM `fidelity` where year(fidCurDate) = '2022' and week(fidCurDate) = week(CURRENT_DATE)-1 order by fidCurDate DESC

-- comment
SELECT  fidId, fidCurDate, Year(fidCurDate), week(fidCurDate), fidTot, min(fidCurDate), max(fidCurDate)   FROM `fidelity` where year(fidCurDate) = '2022' and  week(fidCurDate) = week(CURRENT_DATE)-1 order by fidCurDate desc

-- Difference between two records
select fidId, fidTot from fideilty limit 2


-- Diff between max/min not working

SELECT fidId, format(fidTot,2),  FORMAT(max(fidTot),2) as Maximum , FORMAT(min(fidTot),2) as Minimum,
    FORMAT(MAX(FIDTOT)-MIN(FIDTOT),2)
 as Differ
FROM fidelity  order by fidCurDate desc limit 5
-- get min max per year
SELECT COUNT(fidId) as Records, fidCUrDate, fidId, Max(FidTot) as MaxTotal, min(fidTot) as MinTotal
FROM fidelity

GROUP BY EXTRACT(YEAR FROM fidCurDate)
--- Min/Max by Year by Month
SELECT Year(fidCurDate)as Year, MonthName(fidCurDate) as Month,  Max(FidTot) as MaxTotal, min(fidTot) as MinTotal, fidCUrDate,COUNT(fidId) as DaysOfMonth
FROM fidelity
-- where Year(fidCurDate) = '2022'
GROUP BY EXTRACT(YEAR_MONTH FROM fidCurDate)

 order by Year(fidCurDate)DESC
	Link: https://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year

	-- Weekly Values
	select week(fidCurDate), fidTot, fidCurDate
from fidelity
where
-- -1 is for previous week
week(fidCurDate) = week(CURRENT_DATE)-1 and year(fidCurDate) = '2022'
order by fidCurDate desc

-- Weekly min/max
select week(fidCurDate), min(fidTot), max(fidTot), fidTot, fidCurDate
from fidelity
where
week(fidCurDate) = week(CURRENT_DATE)-1 and year(fidCurDate) = '2022'
order by fidCurDate desc

-- get rank or rowId of records returned to get first and last
SELECT t.fidUserId,t.fidId,fidTot,fidCurDate,
       @rownum := @rownum +1 AS rank
  FROM fidelity t
  JOIN (SELECT @rownum := 0) r
 WHERE week(t.fidCurDate) = '28' and year(t.fidCurDate) = '2022'

 -- Double Query
 SELECT tblactivate_account.activate_account, tblmembers.members
FROM
( SELECT COUNT(activate_account.id) activate_account
  WHERE activate_account.email = :email ) AS tblactivate_account,
( SELECT COUNT(members.id) FROM members
  WHERE members.email = :email ) AS tblmembers


select max(maxR.fidTot), maxR.fidCurDate from 
(select mac1G



++++++++++++++++++++++++++++++++++++++++++
NOT WORKING 
++++++++++++++++++++++++++++++++++++++++++
--- min/max by year
set @year =  '2022';
SELECT min(fidTot) As Lowest, max(fidTot), 'Year' as CurrentYear
FROM `fidelity`
 where year(fidCurDate) = @year group by Year(fidCurDate)L

 ---- Max values year not working
 select fidId, fidTot, fidCurDate
from
(select * from fidelity )
 as x
where YEAR(fidCurDate) = '2022'
group by (`fidCurDate`)  order by (`fidCurDate`) DESC


--- not working max tot
SELECT t1.fidId, t1.fidTot, t2.fidCurDate2
FROM fidelity t1
INNER JOIN
(
    SELECT `fidId`, MAX(fidTot) AS maxTot, Min(fidTot) as minTot, fidCurDate as fidCurDate2
    FROM fidelity
    GROUP BY `fidCurDate`
) t2
    ON t1.`fidId` = t2.`FidId` AND t1.fidTot = t2.maxTot

    where Year(fidCurDate) = '2022'
ORDER BY `t1`.`fidId` ASC
https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results
### SETUP ###
Install: brew mysql install
Start Server: brew services start mysql
Stop Server: brew services stop
Status: mysqlsudo systemctl status

### Recover Password ###

Start Server: brew services start mysql
--safe 

GET USERS: SELECT User FROM mysql.user;
CREATE USER
rbutareOld@localhost
CREATE USER 'rbutareOld'@'localhost';
GRANT ALL PRIVILEGES ON dbtest.* TO 'rbutareOld@hostname' IDENTIFIED BY 'DrumHill1;';

CHANGE PASSWORD
ALTER USER wefixy5_drumhill@localhost IDENTIFIED BY 'DrumHill1;';
ALTER USER root@localhost IDENTIFIED BY 'DrumHill1;';
kk