Oracle - SQL developer query using count

Question

I have this table called, ACTIVITIES:

Name      Activity     Minutes
JOSE      videogames      50
MARISSA   videogames      50
TINA      CHESS           90
JAKE      CHESS           95

For each activity in the table, how do I make a count of how many persons did that activity? I would like to print the activity, the count, and the sum of the minutes in my query.

I tried working by parts & this was my attempt:

SELECT Distinct(Activity), count(Activity) as Total
FROM ACTIVITIES;

However, the query result gave me an error saying: "not a single-group group function"

since you need the totals by activity, add GROUP BY Activity, and you don't need DISTINCT(Activity); so try this: SELECT Activity, count(Activity), sum(NUMOFMINUTES) FROM Activities GROUP BY Activity — Burhan Khalid
– Burhan Khalid, Commented Dec 8, 2014 at 4:15

Vincent Ramdhanie · Accepted Answer · 2014-12-08 04:15:31Z

1

You can use a group by clause to first group the table by activity then do the count and the sum. For example:

SELECT count(*), sum(NUMOFMINUTES), Activity 
FROM activities
GROUP BY activity

answered Dec 8, 2014 at 4:15

Vincent Ramdhanie

103k23 gold badges143 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shree.pat18 · Accepted Answer · 2014-12-08 04:15:56Z

0

Use group by to get the count/sum per activity like so:

select activity, count (activity) as totalcount, sum(minutes) as totalminutes
from activities
group by activity

If you want to get some aggregate value such as a count of a specific value, you first need to group the data into subsets based on the distinct values of that column. That is what that error message was referring to. Note that when you group by activity, the records will consider and display only the distinct values of activity, so you won't need to use the distinct keyword.

answered Dec 8, 2014 at 4:15

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

Comments

Twin Tower · Accepted Answer · 2014-12-08 04:20:45Z

0

TRY this SELECT DISTINCT(Activity),COUNT(Activity) AS Total FROM ACTIVITIES GROUP BY Activity

answered Dec 8, 2014 at 4:20

Twin Tower

301 bronze badge

Comments

Abhilash Mandaliya · Accepted Answer · 2014-12-08 15:42:30Z

0

Whenever you use any agreegate function and retrieves another field with it on which you are not performing agreegation then you have to use group by clause with non-agreegate field. so add 'group by Activity' at the end of your query.

edited Dec 8, 2014 at 15:42

answered Dec 8, 2014 at 4:16

Abhilash Mandaliya

428 bronze badges

1 Comment

shree.pat18 Over a year ago

Not exactly. You can do select count(*) from tablename and it will run just fine. group by is required when you want to drill down by specific values.

Collectives™ on Stack Overflow

Oracle - SQL developer query using count

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related