question

faizanahmad23 avatar image
faizanahmad23 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
oracle-sql-developer
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

For sql you could use something like this: SELECT DATEPART(YEAR, Create_time), COUNT(CREATE_TIME) FROM TRACS_DW1.VTM024TROUBLE_TKT GROUP BY DATEPART(YEAR, Create_time) Not near my machine to test the syntax unfortunately :(
0 Likes 0 ·
@faizanahmad23 I am almost positive that you did not actually answer the first question, the syntax does not appear to be correct.
0 Likes 0 ·
@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.
0 Likes 0 ·

1 Answer

· Write an Answer
faizanahmad23 avatar image
faizanahmad23 answered
![@Oleg : I actually did, somehow got it working :) @][1] [1]: /storage/temp/4231-capture.png

capture.png (29.3 KiB)
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Oleg : I actually did. I somehow got the 1st one working :)
0 Likes 0 ·
@anthony.green : I am using Toad , for writing SQL
0 Likes 0 ·
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?
0 Likes 0 ·
@anthony.green : I have tried but no luck
0 Likes 0 ·
@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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.