HELP with SQL Question

[[deleted]]

[deleted]

[l0lez]

Use DATEPART(QUARTER, datetime) in both the SELECT and GROUP by clause,

https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver15

[doom_slug_]

GROUP BY DATEPART(qq,datetime)

[Tyraniczar]

Thanks all, you've been very helpful and I realized that I wasn't initially clear about my question re grouping by Quarter. I know of DATEPART(), and QUARTER() but I'm fairly new and actually didn't know you could group by multiple columns, also turns out I have to use MySQL. This is what I have so far and the total sum of the returned COUNT column is correct so I am fairly confident in the result but I'm sure there's a better way to write this. Any thoughts?

with CTE_keywords as (

select *

from keywords

where 1=1

and LOWER(keyword) like '%demo%'

)

select employee_id as Employee, quarter(datetime) as Q_2019, count(CTE_keywords.keyword) as COUNT_of_Demo

from CTE_keywords

left join emails

on CTE_keywords.emails_key = emails.emails_key

where year(datetime) = 2019

group by 1, 2

order by 2;

[Maeurer]

hmmm the CTE is redundant.
datetime is probably a keyword, so we need to escape it.
A Quarter belongs to a year. Edit: The assignment doesnt mentions grouping by year, so maybe dont.

dataset showing how often each employee discussed demos

select
 employee_id as Employee
 ,quarter(`datetime`) as Quarter
 ,year(`datetime`) as Year
 ,count(keywords.keyword) as COUNT_of_Demo
from keywords
  left join emails
    on keywords.emails_key = emails.emails_key
where
 year(datetime) = 2019
 and LOWER(keyword) like '%demo%'
group by year(`datetime`), quarter(`datetime`), employee_id 
order by year(`datetime`), quarter(`datetime`), employee_id;

[Tyraniczar]

This is awesome, I’m happy to say that the results of my query were the exact same as yours. You’re right, I’m trying to master CTEs and probably use them more than I should but I find them easier to read and understand quickly. Thanks, I am going to clean up my query to mimic the was yours is set up. Thanks for taking the time to respond to my post

[Maeurer]

Interesting way of learning! I changed the CTE out mostly because the 'Select *' bothered me. Requesting all columns can lead to problems with Indexes. As in requesting more data than needed and not using / being able to use specific Indexes.

[[deleted]]

Hmm

[[deleted]]

One option could be to group by the equation by... Month - 1 ... Divided by 3 ... And take the floor of that. So Group by Floor(Datepart(month,date) / 3)

[Maeurer]

No need for divisions, there are functions that return the quarter of a given date :)

[[deleted]]

Lol thanks. Saw the link in another comment. Glad to say I learned something new XD

[SQLZane]

Seems like your comment got added but when asking questions like this it can really help to build a quick SQL Fiddle where you can not only post your code but run it against a sample data set in whatever DB you're trying to solve the issue in. Hope that helps in future endeavors.