MYSQL returning the count of how many people logged an event










0















https://imgur.com/O1v3O8w



The above image shows my first table called events.



https://imgur.com/7Hbsitl



This above table is linked to the events table and is used to check if a user logs an event. I want to obtain a count of how much each event was logged while displaying the event details. The problem I've encountered is that if an event has 0 logs it will not appear in the output.



This is the sql ive used:



SELECT event.`eventid`,
event.`name`,
`eventtime`,
`location`,
`datecreated`,
`dateofevent`,
`categoryname`,
`description`,
Count(participantlogs.eventid)
FROM event,
recreationalcategory,
participantlogs
WHERE recreationalcategory.categoryid = event.categoryid
AND participantlogs.eventid = event.eventid
GROUP BY event.eventid


Any help would be greatly appreciated!










share|improve this question



















  • 1





    No images please; use formatted text for posting your sample data and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?

    – Madhur Bhaiya
    Nov 13 '18 at 20:43






  • 1





    Possible duplicate of Displaying rows with count 0 with mysql group by

    – Ruud Helderman
    Nov 13 '18 at 20:45











  • A LEFT JOIN is probably what you're looking for. This way it will include the events that have 0 logs. Check out this similar question.

    – Wrokar
    Nov 13 '18 at 20:45






  • 1





    Please don't use Old comma based Implicit joins and use Modern Explicit Join based syntax

    – Madhur Bhaiya
    Nov 13 '18 at 20:49






  • 2





    Invalid GROUP BY... You typically GROUP BY all the columns you select, except those who are arguments to set functions.

    – jarlh
    Nov 13 '18 at 20:59















0















https://imgur.com/O1v3O8w



The above image shows my first table called events.



https://imgur.com/7Hbsitl



This above table is linked to the events table and is used to check if a user logs an event. I want to obtain a count of how much each event was logged while displaying the event details. The problem I've encountered is that if an event has 0 logs it will not appear in the output.



This is the sql ive used:



SELECT event.`eventid`,
event.`name`,
`eventtime`,
`location`,
`datecreated`,
`dateofevent`,
`categoryname`,
`description`,
Count(participantlogs.eventid)
FROM event,
recreationalcategory,
participantlogs
WHERE recreationalcategory.categoryid = event.categoryid
AND participantlogs.eventid = event.eventid
GROUP BY event.eventid


Any help would be greatly appreciated!










share|improve this question



















  • 1





    No images please; use formatted text for posting your sample data and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?

    – Madhur Bhaiya
    Nov 13 '18 at 20:43






  • 1





    Possible duplicate of Displaying rows with count 0 with mysql group by

    – Ruud Helderman
    Nov 13 '18 at 20:45











  • A LEFT JOIN is probably what you're looking for. This way it will include the events that have 0 logs. Check out this similar question.

    – Wrokar
    Nov 13 '18 at 20:45






  • 1





    Please don't use Old comma based Implicit joins and use Modern Explicit Join based syntax

    – Madhur Bhaiya
    Nov 13 '18 at 20:49






  • 2





    Invalid GROUP BY... You typically GROUP BY all the columns you select, except those who are arguments to set functions.

    – jarlh
    Nov 13 '18 at 20:59













0












0








0








https://imgur.com/O1v3O8w



The above image shows my first table called events.



https://imgur.com/7Hbsitl



This above table is linked to the events table and is used to check if a user logs an event. I want to obtain a count of how much each event was logged while displaying the event details. The problem I've encountered is that if an event has 0 logs it will not appear in the output.



This is the sql ive used:



SELECT event.`eventid`,
event.`name`,
`eventtime`,
`location`,
`datecreated`,
`dateofevent`,
`categoryname`,
`description`,
Count(participantlogs.eventid)
FROM event,
recreationalcategory,
participantlogs
WHERE recreationalcategory.categoryid = event.categoryid
AND participantlogs.eventid = event.eventid
GROUP BY event.eventid


Any help would be greatly appreciated!










share|improve this question
















https://imgur.com/O1v3O8w



The above image shows my first table called events.



https://imgur.com/7Hbsitl



This above table is linked to the events table and is used to check if a user logs an event. I want to obtain a count of how much each event was logged while displaying the event details. The problem I've encountered is that if an event has 0 logs it will not appear in the output.



This is the sql ive used:



SELECT event.`eventid`,
event.`name`,
`eventtime`,
`location`,
`datecreated`,
`dateofevent`,
`categoryname`,
`description`,
Count(participantlogs.eventid)
FROM event,
recreationalcategory,
participantlogs
WHERE recreationalcategory.categoryid = event.categoryid
AND participantlogs.eventid = event.eventid
GROUP BY event.eventid


Any help would be greatly appreciated!







mysql sql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 13 '18 at 22:00









dchi

212




212










asked Nov 13 '18 at 20:38









Kiran BalrajKiran Balraj

31




31







  • 1





    No images please; use formatted text for posting your sample data and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?

    – Madhur Bhaiya
    Nov 13 '18 at 20:43






  • 1





    Possible duplicate of Displaying rows with count 0 with mysql group by

    – Ruud Helderman
    Nov 13 '18 at 20:45











  • A LEFT JOIN is probably what you're looking for. This way it will include the events that have 0 logs. Check out this similar question.

    – Wrokar
    Nov 13 '18 at 20:45






  • 1





    Please don't use Old comma based Implicit joins and use Modern Explicit Join based syntax

    – Madhur Bhaiya
    Nov 13 '18 at 20:49






  • 2





    Invalid GROUP BY... You typically GROUP BY all the columns you select, except those who are arguments to set functions.

    – jarlh
    Nov 13 '18 at 20:59












  • 1





    No images please; use formatted text for posting your sample data and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?

    – Madhur Bhaiya
    Nov 13 '18 at 20:43






  • 1





    Possible duplicate of Displaying rows with count 0 with mysql group by

    – Ruud Helderman
    Nov 13 '18 at 20:45











  • A LEFT JOIN is probably what you're looking for. This way it will include the events that have 0 logs. Check out this similar question.

    – Wrokar
    Nov 13 '18 at 20:45






  • 1





    Please don't use Old comma based Implicit joins and use Modern Explicit Join based syntax

    – Madhur Bhaiya
    Nov 13 '18 at 20:49






  • 2





    Invalid GROUP BY... You typically GROUP BY all the columns you select, except those who are arguments to set functions.

    – jarlh
    Nov 13 '18 at 20:59







1




1





No images please; use formatted text for posting your sample data and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?

– Madhur Bhaiya
Nov 13 '18 at 20:43





No images please; use formatted text for posting your sample data and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?

– Madhur Bhaiya
Nov 13 '18 at 20:43




1




1





Possible duplicate of Displaying rows with count 0 with mysql group by

– Ruud Helderman
Nov 13 '18 at 20:45





Possible duplicate of Displaying rows with count 0 with mysql group by

– Ruud Helderman
Nov 13 '18 at 20:45













A LEFT JOIN is probably what you're looking for. This way it will include the events that have 0 logs. Check out this similar question.

– Wrokar
Nov 13 '18 at 20:45





A LEFT JOIN is probably what you're looking for. This way it will include the events that have 0 logs. Check out this similar question.

– Wrokar
Nov 13 '18 at 20:45




1




1





Please don't use Old comma based Implicit joins and use Modern Explicit Join based syntax

– Madhur Bhaiya
Nov 13 '18 at 20:49





Please don't use Old comma based Implicit joins and use Modern Explicit Join based syntax

– Madhur Bhaiya
Nov 13 '18 at 20:49




2




2





Invalid GROUP BY... You typically GROUP BY all the columns you select, except those who are arguments to set functions.

– jarlh
Nov 13 '18 at 20:59





Invalid GROUP BY... You typically GROUP BY all the columns you select, except those who are arguments to set functions.

– jarlh
Nov 13 '18 at 20:59












2 Answers
2






active

oldest

votes


















1














Ditch the old-school comma syntax for the join operation, and use JOIN keyword instead, relocate the join predicates to the ON clause. The JOIN keyword can be modified to with LEFT or RIGHT keyword to allow an outer join.



An outer join will return rows that don't have a match; exactly the use case described.



SELECT e.eventid
, e.name
, COUNT(p.eventid)
FROM event e
LEFT
JOIN recreationalcategory c
ON c.categoryid = e.categoryid
LEFT
JOIN participantlogs p
ON p.eventid = e.eventid
GROUP
BY e.eventid
, e.name



Another alternative would be to use a correlated subquery in the SELECT list rather than a join. Assuming eventid is PRIMARY KEY or UNIQUE KEY in event table, we can eliminate the GROUP BY clause...



SELECT e.eventid
, e.name
, ( SELECT COUNT(1)
FROM participantlogs p
WHERE p.eventid = e.eventid
) AS cnt
FROM event e
LEFT
JOIN recreationalcategory c
ON c.categoryid = e.categoryid





share|improve this answer

























  • This worked as well thanks

    – Kiran Balraj
    Nov 13 '18 at 21:59


















0














A left join should do your job.



SELECT event.`eventid`,
event.`name`,
`eventtime`,
`location`,
`datecreated`,
`dateofevent`,
`categoryname`,
`description`,
Count(participantlogs.eventid)
FROM event,
recreationalcategory,
participantlogs
WHERE (recreationalcategory.categoryid = event.categoryid OR recreationalcategory.categoryid is null)
AND participantlogs.eventid = event.eventid
GROUP BY event.eventid


on simply use left join



SELECT event.`eventid`,
event.`name`,
`eventtime`,
`location`,
`datecreated`,
`dateofevent`,
`categoryname`,
`description`,
Count(participantlogs.eventid)
FROM event
left join recreationalcategory
on recreationalcategory.categoryid = event.categoryid
join participantlo on
on participantlogs.eventid = event.eventid
GROUP BY event.eventid





share|improve this answer























  • invalid GROUP BY which can cause invalid results on MySQL without sql mode only_full_group_by.

    – Raymond Nijland
    Nov 13 '18 at 21:28











  • This worked Thanks!

    – Kiran Balraj
    Nov 13 '18 at 21:58










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


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53289137%2fmysql-returning-the-count-of-how-many-people-logged-an-event%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown

























2 Answers
2






active

oldest

votes








2 Answers
2






active

oldest

votes









active

oldest

votes






active

oldest

votes









1














Ditch the old-school comma syntax for the join operation, and use JOIN keyword instead, relocate the join predicates to the ON clause. The JOIN keyword can be modified to with LEFT or RIGHT keyword to allow an outer join.



An outer join will return rows that don't have a match; exactly the use case described.



SELECT e.eventid
, e.name
, COUNT(p.eventid)
FROM event e
LEFT
JOIN recreationalcategory c
ON c.categoryid = e.categoryid
LEFT
JOIN participantlogs p
ON p.eventid = e.eventid
GROUP
BY e.eventid
, e.name



Another alternative would be to use a correlated subquery in the SELECT list rather than a join. Assuming eventid is PRIMARY KEY or UNIQUE KEY in event table, we can eliminate the GROUP BY clause...



SELECT e.eventid
, e.name
, ( SELECT COUNT(1)
FROM participantlogs p
WHERE p.eventid = e.eventid
) AS cnt
FROM event e
LEFT
JOIN recreationalcategory c
ON c.categoryid = e.categoryid





share|improve this answer

























  • This worked as well thanks

    – Kiran Balraj
    Nov 13 '18 at 21:59















1














Ditch the old-school comma syntax for the join operation, and use JOIN keyword instead, relocate the join predicates to the ON clause. The JOIN keyword can be modified to with LEFT or RIGHT keyword to allow an outer join.



An outer join will return rows that don't have a match; exactly the use case described.



SELECT e.eventid
, e.name
, COUNT(p.eventid)
FROM event e
LEFT
JOIN recreationalcategory c
ON c.categoryid = e.categoryid
LEFT
JOIN participantlogs p
ON p.eventid = e.eventid
GROUP
BY e.eventid
, e.name



Another alternative would be to use a correlated subquery in the SELECT list rather than a join. Assuming eventid is PRIMARY KEY or UNIQUE KEY in event table, we can eliminate the GROUP BY clause...



SELECT e.eventid
, e.name
, ( SELECT COUNT(1)
FROM participantlogs p
WHERE p.eventid = e.eventid
) AS cnt
FROM event e
LEFT
JOIN recreationalcategory c
ON c.categoryid = e.categoryid





share|improve this answer

























  • This worked as well thanks

    – Kiran Balraj
    Nov 13 '18 at 21:59













1












1








1







Ditch the old-school comma syntax for the join operation, and use JOIN keyword instead, relocate the join predicates to the ON clause. The JOIN keyword can be modified to with LEFT or RIGHT keyword to allow an outer join.



An outer join will return rows that don't have a match; exactly the use case described.



SELECT e.eventid
, e.name
, COUNT(p.eventid)
FROM event e
LEFT
JOIN recreationalcategory c
ON c.categoryid = e.categoryid
LEFT
JOIN participantlogs p
ON p.eventid = e.eventid
GROUP
BY e.eventid
, e.name



Another alternative would be to use a correlated subquery in the SELECT list rather than a join. Assuming eventid is PRIMARY KEY or UNIQUE KEY in event table, we can eliminate the GROUP BY clause...



SELECT e.eventid
, e.name
, ( SELECT COUNT(1)
FROM participantlogs p
WHERE p.eventid = e.eventid
) AS cnt
FROM event e
LEFT
JOIN recreationalcategory c
ON c.categoryid = e.categoryid





share|improve this answer















Ditch the old-school comma syntax for the join operation, and use JOIN keyword instead, relocate the join predicates to the ON clause. The JOIN keyword can be modified to with LEFT or RIGHT keyword to allow an outer join.



An outer join will return rows that don't have a match; exactly the use case described.



SELECT e.eventid
, e.name
, COUNT(p.eventid)
FROM event e
LEFT
JOIN recreationalcategory c
ON c.categoryid = e.categoryid
LEFT
JOIN participantlogs p
ON p.eventid = e.eventid
GROUP
BY e.eventid
, e.name



Another alternative would be to use a correlated subquery in the SELECT list rather than a join. Assuming eventid is PRIMARY KEY or UNIQUE KEY in event table, we can eliminate the GROUP BY clause...



SELECT e.eventid
, e.name
, ( SELECT COUNT(1)
FROM participantlogs p
WHERE p.eventid = e.eventid
) AS cnt
FROM event e
LEFT
JOIN recreationalcategory c
ON c.categoryid = e.categoryid






share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 13 '18 at 20:59

























answered Nov 13 '18 at 20:54









spencer7593spencer7593

85.2k108096




85.2k108096












  • This worked as well thanks

    – Kiran Balraj
    Nov 13 '18 at 21:59

















  • This worked as well thanks

    – Kiran Balraj
    Nov 13 '18 at 21:59
















This worked as well thanks

– Kiran Balraj
Nov 13 '18 at 21:59





This worked as well thanks

– Kiran Balraj
Nov 13 '18 at 21:59













0














A left join should do your job.



SELECT event.`eventid`,
event.`name`,
`eventtime`,
`location`,
`datecreated`,
`dateofevent`,
`categoryname`,
`description`,
Count(participantlogs.eventid)
FROM event,
recreationalcategory,
participantlogs
WHERE (recreationalcategory.categoryid = event.categoryid OR recreationalcategory.categoryid is null)
AND participantlogs.eventid = event.eventid
GROUP BY event.eventid


on simply use left join



SELECT event.`eventid`,
event.`name`,
`eventtime`,
`location`,
`datecreated`,
`dateofevent`,
`categoryname`,
`description`,
Count(participantlogs.eventid)
FROM event
left join recreationalcategory
on recreationalcategory.categoryid = event.categoryid
join participantlo on
on participantlogs.eventid = event.eventid
GROUP BY event.eventid





share|improve this answer























  • invalid GROUP BY which can cause invalid results on MySQL without sql mode only_full_group_by.

    – Raymond Nijland
    Nov 13 '18 at 21:28











  • This worked Thanks!

    – Kiran Balraj
    Nov 13 '18 at 21:58















0














A left join should do your job.



SELECT event.`eventid`,
event.`name`,
`eventtime`,
`location`,
`datecreated`,
`dateofevent`,
`categoryname`,
`description`,
Count(participantlogs.eventid)
FROM event,
recreationalcategory,
participantlogs
WHERE (recreationalcategory.categoryid = event.categoryid OR recreationalcategory.categoryid is null)
AND participantlogs.eventid = event.eventid
GROUP BY event.eventid


on simply use left join



SELECT event.`eventid`,
event.`name`,
`eventtime`,
`location`,
`datecreated`,
`dateofevent`,
`categoryname`,
`description`,
Count(participantlogs.eventid)
FROM event
left join recreationalcategory
on recreationalcategory.categoryid = event.categoryid
join participantlo on
on participantlogs.eventid = event.eventid
GROUP BY event.eventid





share|improve this answer























  • invalid GROUP BY which can cause invalid results on MySQL without sql mode only_full_group_by.

    – Raymond Nijland
    Nov 13 '18 at 21:28











  • This worked Thanks!

    – Kiran Balraj
    Nov 13 '18 at 21:58













0












0








0







A left join should do your job.



SELECT event.`eventid`,
event.`name`,
`eventtime`,
`location`,
`datecreated`,
`dateofevent`,
`categoryname`,
`description`,
Count(participantlogs.eventid)
FROM event,
recreationalcategory,
participantlogs
WHERE (recreationalcategory.categoryid = event.categoryid OR recreationalcategory.categoryid is null)
AND participantlogs.eventid = event.eventid
GROUP BY event.eventid


on simply use left join



SELECT event.`eventid`,
event.`name`,
`eventtime`,
`location`,
`datecreated`,
`dateofevent`,
`categoryname`,
`description`,
Count(participantlogs.eventid)
FROM event
left join recreationalcategory
on recreationalcategory.categoryid = event.categoryid
join participantlo on
on participantlogs.eventid = event.eventid
GROUP BY event.eventid





share|improve this answer













A left join should do your job.



SELECT event.`eventid`,
event.`name`,
`eventtime`,
`location`,
`datecreated`,
`dateofevent`,
`categoryname`,
`description`,
Count(participantlogs.eventid)
FROM event,
recreationalcategory,
participantlogs
WHERE (recreationalcategory.categoryid = event.categoryid OR recreationalcategory.categoryid is null)
AND participantlogs.eventid = event.eventid
GROUP BY event.eventid


on simply use left join



SELECT event.`eventid`,
event.`name`,
`eventtime`,
`location`,
`datecreated`,
`dateofevent`,
`categoryname`,
`description`,
Count(participantlogs.eventid)
FROM event
left join recreationalcategory
on recreationalcategory.categoryid = event.categoryid
join participantlo on
on participantlogs.eventid = event.eventid
GROUP BY event.eventid






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 13 '18 at 20:55









Harshad_PardeshiHarshad_Pardeshi

326




326












  • invalid GROUP BY which can cause invalid results on MySQL without sql mode only_full_group_by.

    – Raymond Nijland
    Nov 13 '18 at 21:28











  • This worked Thanks!

    – Kiran Balraj
    Nov 13 '18 at 21:58

















  • invalid GROUP BY which can cause invalid results on MySQL without sql mode only_full_group_by.

    – Raymond Nijland
    Nov 13 '18 at 21:28











  • This worked Thanks!

    – Kiran Balraj
    Nov 13 '18 at 21:58
















invalid GROUP BY which can cause invalid results on MySQL without sql mode only_full_group_by.

– Raymond Nijland
Nov 13 '18 at 21:28





invalid GROUP BY which can cause invalid results on MySQL without sql mode only_full_group_by.

– Raymond Nijland
Nov 13 '18 at 21:28













This worked Thanks!

– Kiran Balraj
Nov 13 '18 at 21:58





This worked Thanks!

– Kiran Balraj
Nov 13 '18 at 21:58

















draft saved

draft discarded
















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53289137%2fmysql-returning-the-count-of-how-many-people-logged-an-event%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Darth Vader #20

How to how show current date and time by default on contact form 7 in WordPress without taking input from user in datetimepicker

Ondo