SQL Query

Hi All

I have a SQL query (below) that shows me stats of feedback table, however there is another column within the feedback table called FBDate and I wanted to use that so I can filter it out monthly.

SQL Query
SELECT 
    COUNT(CASE WHEN fbscore = 1 then 1 ELSE NULL END) as "Awesome",
    COUNT(CASE WHEN fbscore = 2 then 1 ELSE NULL END) as "Good",
    COUNT(CASE WHEN fbscore = 3 then 1 ELSE NULL END) as "OK",
    COUNT(CASE WHEN fbscore = 4 then 1 ELSE NULL END) as "Bad"
from feedback

Open in new window


Output
Screenshot attached.
SQL Query output
Any suggestion would help.
Imran ShabirAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
So to just select the January 2018 data you can do:

SELECT 
    COUNT(CASE WHEN fbscore = 1 then 1 ELSE NULL END) as "Awesome",
    COUNT(CASE WHEN fbscore = 2 then 1 ELSE NULL END) as "Good",
    COUNT(CASE WHEN fbscore = 3 then 1 ELSE NULL END) as "OK",
    COUNT(CASE WHEN fbscore = 4 then 1 ELSE NULL END) as "Bad"
FROM feedback
WHERE FBDate >= '1/1/2018' AND FBDate < '2/1/2018'

Open in new window


»bp
Brian CroweDatabase AdministratorCommented:
Another option if you want the data presented monthly...

SELECT CAST(CONVERT(VARCHAR(6), fbDate, 112) AS INT) AS YearMonth,
    COUNT(CASE WHEN fbscore = 1 then 1 ELSE NULL END) as "Awesome",
    COUNT(CASE WHEN fbscore = 2 then 1 ELSE NULL END) as "Good",
    COUNT(CASE WHEN fbscore = 3 then 1 ELSE NULL END) as "OK",
    COUNT(CASE WHEN fbscore = 4 then 1 ELSE NULL END) as "Bad"
FROM feedback
GROUP BY CAST(CONVERT(VARCHAR(6), fbDate, 112) AS INT)
ORDER BY CAST(CONVERT(VARCHAR(6), fbDate, 112) AS INT)

Open in new window

Imran ShabirAuthor Commented:
@Bill
I did try that but i didnt want to keep editing the date range.

@Brian
I tried that and got this error:
Invalid SQL Statement: failed with error: the order by clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

However, i took the order by out and it worked, is it possible to change the format ie from 201803 to 03/2018 of 03-2018?
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

larryhSr. Software EngineerCommented:
SELECT CONVERT(VARCHAR,DATEPART(Month,FBDate))+'/'+CONVERT(VARCHAR,DATEPART(YEAR,FBDate)) AS YearMonth,
    COUNT(CASE WHEN fbscore = 1 then 1 ELSE NULL END) as "Awesome",
    COUNT(CASE WHEN fbscore = 2 then 1 ELSE NULL END) as "Good",
    COUNT(CASE WHEN fbscore = 3 then 1 ELSE NULL END) as "OK",
    COUNT(CASE WHEN fbscore = 4 then 1 ELSE NULL END) as "Bad"
FROM feedback
GROUP BY CAST(CONVERT(VARCHAR(6), fbDate, 112) AS INT)

Open in new window

Imran ShabirAuthor Commented:
Hi @Larry

That doesn't work.

SQL Error
Brian CroweDatabase AdministratorCommented:
First, I'll say that formatting of dates should be handled by the presentation layer (report or application).

That said try...

SELECT CONVERT(VARCHAR,DATEPART(Month,FBDate))+'/'+CONVERT(VARCHAR,DATEPART(YEAR,FBDate)) AS YearMonth,
    COUNT(CASE WHEN fbscore = 1 then 1 ELSE NULL END) as "Awesome",
    COUNT(CASE WHEN fbscore = 2 then 1 ELSE NULL END) as "Good",
    COUNT(CASE WHEN fbscore = 3 then 1 ELSE NULL END) as "OK",
    COUNT(CASE WHEN fbscore = 4 then 1 ELSE NULL END) as "Bad"
FROM feedback
GROUP BY CONVERT(VARCHAR,DATEPART(Month,FBDate))+'/'+CONVERT(VARCHAR,DATEPART(YEAR,FBDate))

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
larryhSr. Software EngineerCommented:
It looks like Brian's answer is the best one here.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.