how to INSERT new record with SELECT value in sql

How can i insert a value into table A using return value from table B. I know i can use INSERT INTO SELECT statement but this requires that data types in source and target tables match and the columns are in order but i have the below table like thus:

Table A:

Table B:

Now i want to count all data from Table A which should return 8 and save it in total field in Table B, and count data which user_id is greater than 0 which should return 5 and save it in total_log_in field and also count data which user_id i null which should return 3 and save it in total_un_log. I used the below code but get error plase any help on how to do this thanks

 INSERT INTO `user_tracking_daily`(`total`, `total_log_in`, `total_un_log`, `date`) VALUES (SELECT COUNT(*) from `session`,
SELECT COUNT(*) from `session` where `user_id` > 0,SELECT COUNT(*) from `session` where `user_id` IS NULL,now());

Answer

I would write this as an INSERT ... SELECT ... statement with an aggregate query.

INSERT INTO `user_tracking_daily`(`total`, `total_log_in`, `total_un_log`, `date`) 
SELECT
    COUNT(*),
    SUM(user_id > 0),
    SUM(user_ID IS NULL),
    NOW()
FROM session

Advertisement

Answer