0

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

3
Contributors
3
Replies
4
Views
5 Years
Discussion Span
Last Post by stanley87
0

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
0

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;
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.