asked
SQL group by count and extract
1. Run a query that returns the number of trouble tickets in the VTM024… table, but only the ones created in the year 2015. Note: when setting the where criteria, use a function to extract the Year portion from the timestamp field. 2. Using the Year function in the first answer, write SQL that shows the number of trouble tickets created in each year dynamically, and do this using a “GROUP BY” clause in the SQL. It should return something like: 2012 10 2013 54 2014 111 etc I have the answer for 1st question: 1.SELECT COUNT(CREATE_TIME) FROM TRACS_DW1.VTM024TROUBLE_TKT WHERE EXTRACT (YEAR FROM CREATE_TIME) = 2015 Please help me in 2nd question
@faizanahmad23 are you actually using Microsoft SQL Server or are you using Oracle as the solution you posted for Q1 is Oracle PL/SQL syntax. If your using Microsoft SQL Server you will need to re-work the solution for T-SQL. The solution @Adedba gave will give you a good starting point on the re-work.
![@Oleg : I actually did, somehow got it working :) @][1] [1]: /storage/temp/4231-capture.png
OK, that's Toad for Oracle as that query is using PL/SQL as "EXTRACT (YEAR...)" is PL/SQL not T-SQL. This is a Microsoft SQL Server forum but some people do have Oracle skills also, so helps to tag your post correctly as it's tagged for Microsoft. Have you tried looking at the IN clause rather then = and also the syntax for GROUP BY and modifying your Q1 answer accordingly?
@faizanahmad23 If you already answered first question which returns the number of tickets for specific year 2015 then I am not sure what prevents you from simply adding a **GROUP BY** to that statement to get the count for all years. This should answer the second question, right? For example, select extract (year from CREATE_TIME) as TheYear, count(*) as TicketCount from TRACS_DW1.VTM024TROUBLE_TKT group by extract (year from CREATE_TIME) Run the statement above, and this will return the results consisting of 2 columns and as many rows as there are years, listing year in the first column and number of tickets in the second.
20 People are following this question.
