Styjun

Best Ergonomic Design for a handheld ranged weapon

How and when is the best time to reveal to new hires you are trans?

Would people understand me speaking German all over Europe?

May a hotel provide accommodation for fewer people than booked?

Is it unprofessional to mention your cover letter and resume are best viewed in Chrome?

Best practice for keeping temperature constant during film development at home

Rampant sharing of authorship among colleagues in the name of "collaboration". Is not taking part in it a death knell for a future in academia?

Should students have access to past exams or an exam bank?

Can I shorten this filter, that finds disk sizes over 100G?

Were there any unmanned expeditions to the moon that returned to Earth prior to Apollo?

How can flights operated by the same company have such different prices when marketed by another?

What is the oxidation state of Mn in HMn(CO)5?

How do I respond appropriately to an overseas company that obtained a visa for me without hiring me?

What does 「ちんちんかいかい」 mean?

Why is “deal 6 damage” a legit phrase?

Russian pronunciation of /etc (a directory)

Just how much information should you share with a former client?

Applications of pure mathematics in operations research

How to structure presentation to avoid getting questions that will be answered later in the presentation?

Why does Latex make a small adjustment when I change section color

Password management for kids - what's a good way to start?

Stationing Callouts using VBScript Labeling in ArcMap?

Easy way to get process information from a window

How would a lunar colony attack Earth?

Got 3 columns of interest, need to show their graph, using MYSQL

How do you set a default value for a MySQL Datetime column?How to find all the tables in MySQL with specific column names in them?Getting the number of rows with a GROUP BY queryHow do I specify unique constraint for multiple columns in MySQL?How to use count and group by at the same select statementmysql counting records in a single table, help neededGroup results by periodReference - What does this error mean in PHP?Divide MySQL SELECT all into sections of 7 results and run new query on each sectionMySQL - combine record counts having same column value

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

So I have 3 metric columns that are of interest:

OPENED_AT, READ_AT, CLICKED_AT

each of which is a DATETIME stating actions made by the users.

The objective is to show a graph throughout the year segmented into weeks, showing how many users opened, read throughout the content and clicked a button.

Pretty much like this:

My main objective is to stay lean and efficient throughout the code and queries. I'd like to practically divide each color (metric) by the week.
I would like to refrain from using 3 seperate SELECT queries with a different GROUP BY (WEEK_OPENED_AT), then GROUP BY (WEEK_READ_AT) and GROUP BY (WEEK_CLICKED_AT).

My guess was this would work:

SELECT WEEK(`OPENED_AT`) AS WEEK_OPENED_AT, COUNT(`OPENED_AT`) AS 
COUNT_OPENED_AT from `tbl_usage` GROUP BY WEEK_OPENED_AT

then

SELECT WEEK(`READ_AT`) AS WEEK_READ_AT, COUNT(`READ_AT`) AS COUNT_READ_AT 
from `tbl_usage` GROUP BY WEEK_READ_AT

then the last

SELECT WEEK(`CLICKED_AT`) AS WEEK_CLICKED_AT, COUNT(`CLICKED_AT`) AS 
COUNT_CLICKED_AT from `tbl_usage` GROUP BY WEEK_CLICKED_AT

Can I accomplish that result that with a single SELECT?

Thanks upfront

PS: Maybe an option would be to add a running number like 1..52 representing week numbers, so that group by can segment all the same?

The problem is that weeks can pass by without any any clicks, but with many say opens.

mysql sql

share|improve this question

asked Mar 26 at 22:23

TedTed

1,86088 gold badges4343 silver badges8686 bronze badges

add a comment | 

0

So I have 3 metric columns that are of interest:

OPENED_AT, READ_AT, CLICKED_AT

each of which is a DATETIME stating actions made by the users.

The objective is to show a graph throughout the year segmented into weeks, showing how many users opened, read throughout the content and clicked a button.

Pretty much like this:

My main objective is to stay lean and efficient throughout the code and queries. I'd like to practically divide each color (metric) by the week.
I would like to refrain from using 3 seperate SELECT queries with a different GROUP BY (WEEK_OPENED_AT), then GROUP BY (WEEK_READ_AT) and GROUP BY (WEEK_CLICKED_AT).

My guess was this would work:

SELECT WEEK(`OPENED_AT`) AS WEEK_OPENED_AT, COUNT(`OPENED_AT`) AS 
COUNT_OPENED_AT from `tbl_usage` GROUP BY WEEK_OPENED_AT

then

SELECT WEEK(`READ_AT`) AS WEEK_READ_AT, COUNT(`READ_AT`) AS COUNT_READ_AT 
from `tbl_usage` GROUP BY WEEK_READ_AT

then the last

SELECT WEEK(`CLICKED_AT`) AS WEEK_CLICKED_AT, COUNT(`CLICKED_AT`) AS 
COUNT_CLICKED_AT from `tbl_usage` GROUP BY WEEK_CLICKED_AT

Can I accomplish that result that with a single SELECT?

Thanks upfront

PS: Maybe an option would be to add a running number like 1..52 representing week numbers, so that group by can segment all the same?

The problem is that weeks can pass by without any any clicks, but with many say opens.

mysql sql

share|improve this question

asked Mar 26 at 22:23

TedTed

1,86088 gold badges4343 silver badges8686 bronze badges

add a comment | 

So I have 3 metric columns that are of interest:

OPENED_AT, READ_AT, CLICKED_AT

each of which is a DATETIME stating actions made by the users.

The objective is to show a graph throughout the year segmented into weeks, showing how many users opened, read throughout the content and clicked a button.

Pretty much like this:

My main objective is to stay lean and efficient throughout the code and queries. I'd like to practically divide each color (metric) by the week.
I would like to refrain from using 3 seperate SELECT queries with a different GROUP BY (WEEK_OPENED_AT), then GROUP BY (WEEK_READ_AT) and GROUP BY (WEEK_CLICKED_AT).

My guess was this would work:

SELECT WEEK(`OPENED_AT`) AS WEEK_OPENED_AT, COUNT(`OPENED_AT`) AS 
COUNT_OPENED_AT from `tbl_usage` GROUP BY WEEK_OPENED_AT

then

SELECT WEEK(`READ_AT`) AS WEEK_READ_AT, COUNT(`READ_AT`) AS COUNT_READ_AT 
from `tbl_usage` GROUP BY WEEK_READ_AT

then the last

SELECT WEEK(`CLICKED_AT`) AS WEEK_CLICKED_AT, COUNT(`CLICKED_AT`) AS 
COUNT_CLICKED_AT from `tbl_usage` GROUP BY WEEK_CLICKED_AT

Can I accomplish that result that with a single SELECT?

Thanks upfront

PS: Maybe an option would be to add a running number like 1..52 representing week numbers, so that group by can segment all the same?

The problem is that weeks can pass by without any any clicks, but with many say opens.

mysql sql

share|improve this question

asked Mar 26 at 22:23

TedTed

1,86088 gold badges4343 silver badges8686 bronze badges

OPENED_AT, READ_AT, CLICKED_AT

SELECT WEEK(`OPENED_AT`) AS WEEK_OPENED_AT, COUNT(`OPENED_AT`) AS 
COUNT_OPENED_AT from `tbl_usage` GROUP BY WEEK_OPENED_AT

SELECT WEEK(`READ_AT`) AS WEEK_READ_AT, COUNT(`READ_AT`) AS COUNT_READ_AT 
from `tbl_usage` GROUP BY WEEK_READ_AT

SELECT WEEK(`CLICKED_AT`) AS WEEK_CLICKED_AT, COUNT(`CLICKED_AT`) AS 
COUNT_CLICKED_AT from `tbl_usage` GROUP BY WEEK_CLICKED_AT

share|improve this question

asked Mar 26 at 22:23

TedTed

1,86088 gold badges4343 silver badges8686 bronze badges

share|improve this question

asked Mar 26 at 22:23

TedTed

1,86088 gold badges4343 silver badges8686 bronze badges

asked Mar 26 at 22:23

TedTed

1,86088 gold badges4343 silver badges8686 bronze badges

asked Mar 26 at 22:23

TedTed

1,86088 gold badges4343 silver badges8686 bronze badges

1 Answer
1

active

oldest

votes

0

Yes you can. If the columns you show are in the same rows (i.e. same row has both CLICKED_AT and READ_AT values), then you'd need separate counts / joins.

I'd recommend using a recursive CTE to generate the week dates first, then individual CTEs to get each of the aggregate values, and then outer-join those to the week dimension, coalescing NULLs to zeroes.

Example:

WITH RECURSIVE all_weeks(weekStart) AS (
 SELECT CAST('2018-12-31' AS date)
 UNION ALL
 SELECT DATE_ADD(weekStart, INTERVAL 1 WEEK)
 FROM all_months
 WHERE DATE_ADD(weekStart, INTERVAL 1 WEEK) < '2019-04-01'
 )
, op_cnt as (SELECT 
 SUBDATE('OPENED_AT', WEEKDAY('OPENED_AT')) AS WEEK_OPENED_AT, 
 COUNT('OPENED_AT') AS COUNT_OPENED_AT 
 from `tbl_usage` GROUP BY WEEK_OPENED_AT
)
SELECT weekStart, COALESCE(COUNT_OPENED_AT , 0) as cnt_Opens
from all_weeks
LEFT JOIN op_cnt ON (weekStart = WEEK_OPENED_AT)

share|improve this answer

answered Mar 27 at 0:30

MegadestMegadest

31911 silver badge88 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55367017%2fgot-3-columns-of-interest-need-to-show-their-graph-using-mysql%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Yes you can. If the columns you show are in the same rows (i.e. same row has both CLICKED_AT and READ_AT values), then you'd need separate counts / joins.

I'd recommend using a recursive CTE to generate the week dates first, then individual CTEs to get each of the aggregate values, and then outer-join those to the week dimension, coalescing NULLs to zeroes.

Example:

WITH RECURSIVE all_weeks(weekStart) AS (
 SELECT CAST('2018-12-31' AS date)
 UNION ALL
 SELECT DATE_ADD(weekStart, INTERVAL 1 WEEK)
 FROM all_months
 WHERE DATE_ADD(weekStart, INTERVAL 1 WEEK) < '2019-04-01'
 )
, op_cnt as (SELECT 
 SUBDATE('OPENED_AT', WEEKDAY('OPENED_AT')) AS WEEK_OPENED_AT, 
 COUNT('OPENED_AT') AS COUNT_OPENED_AT 
 from `tbl_usage` GROUP BY WEEK_OPENED_AT
)
SELECT weekStart, COALESCE(COUNT_OPENED_AT , 0) as cnt_Opens
from all_weeks
LEFT JOIN op_cnt ON (weekStart = WEEK_OPENED_AT)

share|improve this answer

answered Mar 27 at 0:30

MegadestMegadest

31911 silver badge88 bronze badges

add a comment | 

0

Yes you can. If the columns you show are in the same rows (i.e. same row has both CLICKED_AT and READ_AT values), then you'd need separate counts / joins.

I'd recommend using a recursive CTE to generate the week dates first, then individual CTEs to get each of the aggregate values, and then outer-join those to the week dimension, coalescing NULLs to zeroes.

Example:

WITH RECURSIVE all_weeks(weekStart) AS (
 SELECT CAST('2018-12-31' AS date)
 UNION ALL
 SELECT DATE_ADD(weekStart, INTERVAL 1 WEEK)
 FROM all_months
 WHERE DATE_ADD(weekStart, INTERVAL 1 WEEK) < '2019-04-01'
 )
, op_cnt as (SELECT 
 SUBDATE('OPENED_AT', WEEKDAY('OPENED_AT')) AS WEEK_OPENED_AT, 
 COUNT('OPENED_AT') AS COUNT_OPENED_AT 
 from `tbl_usage` GROUP BY WEEK_OPENED_AT
)
SELECT weekStart, COALESCE(COUNT_OPENED_AT , 0) as cnt_Opens
from all_weeks
LEFT JOIN op_cnt ON (weekStart = WEEK_OPENED_AT)

share|improve this answer

answered Mar 27 at 0:30

MegadestMegadest

31911 silver badge88 bronze badges

add a comment | 

Yes you can. If the columns you show are in the same rows (i.e. same row has both CLICKED_AT and READ_AT values), then you'd need separate counts / joins.

I'd recommend using a recursive CTE to generate the week dates first, then individual CTEs to get each of the aggregate values, and then outer-join those to the week dimension, coalescing NULLs to zeroes.

Example:

WITH RECURSIVE all_weeks(weekStart) AS (
 SELECT CAST('2018-12-31' AS date)
 UNION ALL
 SELECT DATE_ADD(weekStart, INTERVAL 1 WEEK)
 FROM all_months
 WHERE DATE_ADD(weekStart, INTERVAL 1 WEEK) < '2019-04-01'
 )
, op_cnt as (SELECT 
 SUBDATE('OPENED_AT', WEEKDAY('OPENED_AT')) AS WEEK_OPENED_AT, 
 COUNT('OPENED_AT') AS COUNT_OPENED_AT 
 from `tbl_usage` GROUP BY WEEK_OPENED_AT
)
SELECT weekStart, COALESCE(COUNT_OPENED_AT , 0) as cnt_Opens
from all_weeks
LEFT JOIN op_cnt ON (weekStart = WEEK_OPENED_AT)

share|improve this answer

answered Mar 27 at 0:30

MegadestMegadest

31911 silver badge88 bronze badges

WITH RECURSIVE all_weeks(weekStart) AS (
 SELECT CAST('2018-12-31' AS date)
 UNION ALL
 SELECT DATE_ADD(weekStart, INTERVAL 1 WEEK)
 FROM all_months
 WHERE DATE_ADD(weekStart, INTERVAL 1 WEEK) < '2019-04-01'
 )
, op_cnt as (SELECT 
 SUBDATE('OPENED_AT', WEEKDAY('OPENED_AT')) AS WEEK_OPENED_AT, 
 COUNT('OPENED_AT') AS COUNT_OPENED_AT 
 from `tbl_usage` GROUP BY WEEK_OPENED_AT
)
SELECT weekStart, COALESCE(COUNT_OPENED_AT , 0) as cnt_Opens
from all_weeks
LEFT JOIN op_cnt ON (weekStart = WEEK_OPENED_AT)

share|improve this answer

answered Mar 27 at 0:30

MegadestMegadest

31911 silver badge88 bronze badges

answered Mar 27 at 0:30

MegadestMegadest

31911 silver badge88 bronze badges

answered Mar 27 at 0:30

MegadestMegadest

31911 silver badge88 bronze badges