Icy-1-0-6 Icy-1-0-6 - 6 months ago 8
SQL Question

Add Group by and COUNT gives problems

So i'm supposed to make a query that outputs something, now we have been given a already made database + some info inside it.

It has to output who worked at bar ID 8 on the event at 01-MAY-16, with the students name rather than the ID.

The 2 tables needed for this query are


STUDENTS: ID,NAME,ADRESS,BSN,ADRESS2, BANK, SEX


and


ORDERS: ID, DATEORDER, BARS_ID, STUDENTS_ID


Now i've been asked for a query that gives the names of the people that work at bar 8 at 01-MAY-16.

Here is what i came up with and works,

SELECT ORDERS.ID, STUDENTS.NAME, ORDERS.DATEORDER
FROM ORDERS
INNER JOIN STUDENTS
ON ORDERS.STUDENTS_ID=STUDENTS.ID
WHERE (BARS_ID = '8') AND (DATEORDER = '01-MAY-16')
ORDER BY STUDENTS.NAME;


Now it outputs the right data, but the problem is it shows all 1200 orders the students have filled over a night. Instead i want to show just a list with the students that worked there. instead of each record 1 by 1 flying across the screen.

Meaning i have to use
GROUP BY
and
COUNT
, but every time i try this, it won't let me, i've searched high and low, but cant get it working, to a point that its driving me nuts :(

Answer
SELECT STUDENTS.NAME, count(*) 
FROM ORDERS
INNER JOIN STUDENTS
ON ORDERS.STUDENTS_ID = STUDENTS.ID
WHERE BARS_ID = '8' AND DATEORDER = '01-MAY-16'
GROUP BY STUDENTS.NAME;