Question 101454 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – I am trying to write SQL that will let me select 2 columns, transform 1 into 2 and count them

I’m working on a project Where I want to use SQL to select the creation date, creation user and count how many unique instances. The problem is I am change the creation date into 2 separate fields of YEAR and MONTH taking my total columns from 3 to 4 and my SQL results are never correct.

Currenly using MySQL database via MySQL Workbench

Below is the SQL i wrote


SELECT YEAR(Create_Date) AS Year, MONTHNAME(Create_Date) AS Month, Created_By, count(YEAR(Create_Date))AS Total
             FROM appointments 
             GROUP BY Create_Date, Created_By, MONTHNAME(Create_Date), YEAR(Create_Date);

So far it doesn’t return all the "Created_By" user names, And isn’t Grouping the Years or Months for the Count. Pictured Below.

enter image description here

Tags:

2

Answers


  1. 0

    Your SQL query is grouping by Create_Date instead of Year and Month. You need to include the Created_By column in the GROUP BY clause to get the count of unique instances per user like this:

    SELECT YEAR(Create_Date) AS Year, MONTHNAME(Create_Date) AS Month, Created_By, COUNT(DISTINCT CONCAT(YEAR(Create_Date), '-', MONTH(Create_Date))) AS Total
FROM appointments 
GROUP BY Year, Month, Created_By;

    I hope this would work

    Login or Signup to reply.
  2. 0

    generally your group by has to be exactly like your select clause:

    
SELECT YEAR(Create_Date) AS Year, MONTHNAME(Create_Date) AS Month, Created_By, count(YEAR(Create_Date))AS Total
             FROM appointments 
             GROUP BY YEAR(Create_Date), MONTHNAME(Create_Date), Created_By;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search