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 PrewCommented:
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
0
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

0
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?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

0
Imran ShabirAuthor Commented:
Hi @Larry

That doesn't work.

SQL Error
0
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

1

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.
0
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.