How to insert this subqueries into mysql?

[stanley87]

Hi,guys,i cant solve this insert query into ,this is my query,i want it to insert it to another new table.How to solve this query?thank Q very for the help...cheers

SELECT adp.adsPageId,adp.adsSpaceId, DATE_FORMAT(adp.dayClick, '%Y-%m-%d') AS dateReport, 
(SELECT COUNT(*) FROM ads_display att WHERE att.adsSpaceId = adp.adsSpaceId AND att.adsPageId = adp.adsPageId AND att.dayClick = adp.dayClick) as TotalView,
(SELECT COUNT(*) FROM ads_click acc WHERE acc.adsSpaceId = ack.adsSpaceId AND acc.adsPageId = ack.adsPageId AND acc.dayClick = ack.dayClick) as UniqueClick

FROM ads_display adp

LEFT JOIN ads_click ack

ON adp.adsSpaceId = ack.adsSpaceId

AND adp.adsPageId = ack.adsPageId

AND adp.dayClick = ack.dayClick 

GROUP BY dateReport,adp.adsPageId,adp.adsSpaceId

ORDER BY dateReport,adsPageId,adsSpaceId ASC

New table column will be like this

(reportId,adsPageId,adsSpaceId,dateReport,totalView,totalClick)

Note: the reportId is the auto-increment id

[StephNicolaou]

Use the INTO statement after your SQL SELECT statement (..as UniqueClick..) before the FROM elememt.

So .. INTO tableName

tableName will then be created on the fly if not already created, else updated.

http://www.w3schools.com/sql/sql_select_into.asp

[rch1231]

Hello,

It is really very simple but it took me a while to find it too.

create table newtable
SELECT adp.adsPageId,adp.adsSpaceId, DATE_FORMAT(adp.dayClick, '%Y-%m-%d') AS dateReport, 
(SELECT COUNT(*) FROM ads_display att WHERE att.adsSpaceId = adp.adsSpaceId AND att.adsPageId = adp.adsPageId AND att.dayClick = adp.dayClick) as TotalView,
(SELECT COUNT(*) FROM ads_click acc WHERE acc.adsSpaceId = ack.adsSpaceId AND acc.adsPageId = ack.adsPageId AND acc.dayClick = ack.dayClick) as UniqueClick

FROM ads_display adp

LEFT JOIN ads_click ack

ON adp.adsSpaceId = ack.adsSpaceId

AND adp.adsPageId = ack.adsPageId

AND adp.dayClick = ack.dayClick 

GROUP BY dateReport,adp.adsPageId,adp.adsSpaceId

ORDER BY dateReport,adsPageId,adsSpaceId ASC

[stanley87]

thanks for your guys reply,i solve this myself using code below,no doubt it is simple only ==,

INSERT INTO summaryReport(adsPageId,adsSpaceId,dateReport,totalView,totalClick)

SELECT adp.adsPageId,adp.adsSpaceId, DATE_FORMAT(adp.dayClick, '%Y-%m-%d') AS dateReport, 
(SELECT COUNT(*) FROM ads_display att WHERE att.adsSpaceId = adp.adsSpaceId AND att.adsPageId = adp.adsPageId AND att.dayClick = adp.dayClick) as TotalView,
(SELECT COUNT(*) FROM ads_click acc WHERE acc.adsSpaceId = ack.adsSpaceId AND acc.adsPageId = ack.adsPageId AND acc.dayClick = ack.dayClick) as UniqueClick
FROM ads_display adp
LEFT JOIN ads_click ack
ON adp.adsSpaceId = ack.adsSpaceId
AND adp.adsPageId = ack.adsPageId
AND adp.dayClick = ack.dayClick 
GROUP BY dateReport,adp.adsPageId,adp.adsSpaceId
ORDER BY dateReport,adsPageId,adsSpaceId ASC;