Need help with a SubGrouping Query

[comfixit]

I am running the following query:

SELECT from_unixtime(swp.dateline, '%Y %M %D') as datelineDates, s.fullname, count(*) as totalTickets FROM swticketposts swp, swstaff s
WHERE s.staffid=swp.staffid
Group By swp.dateline,s.fullname

And getting results similar to

'2008 December 1st', 'Brandon', '1'
'2008 December 1st', 'Brandon', '1'
'2008 December 1st', 'Joe', '1'
'2008 December 1st', 'Joe', '1'
'2008 December 1st', 'Joe', '1'
'2008 December 1st', 'Joe', '1'
'2008 December 1st', 'Joe', '1'
'2008 December 1st', 'Brandon', '1'
'2008 December 1st', 'Joe', '1'
'2008 December 1st', 'Brandon', '1'
'2008 December 1st', 'Cherie', '1'
'2008 December 1st', 'Brandon', '1'

What I would like is to see something like:

'2008 December 1st', 'Joe', '6'
'2008 December 1st', 'Brandon', '5'
'2008 December 1st', 'Cherie', '1'

Where am I going wrong with this query?

[comfixit]

So I played around a bit with it and think I got it by doing the following:

SELECT from_unixtime(swp.dateline, '%Y %M %D') as datelineDates, s.fullname, count(*) as TotalTickets FROM swticketposts swp, swstaff s
WHERE s.staffid=swp.staffid
Group By datelineDates,s.fullname

[longneck]

that is correct. but do yourself a favor and learn how to use the JOIN keyword instead of listing multiple table in the FROM clause.

[comfixit]

I have used the Join keyword before and find that it seems a bit messy for some of these type of more straightforward SQL statements.

What is the benefit of using the Join keyword over the way I am handling it?

[longneck]

in this particular situation, there is probably minimal benefit.

but in general, using explicit JOINs as much as possible makes queries easier to read and gives the optimizer additional hints as to the most efficient execution plan. it also makes it easier to not forget to specify the relationships between your tables when writing a query.

i typically reserve the comma-delimited table list format for time when i need a Cartesian product.

[r937]

i typically reserve the comma-delimited table list format for time when i need a Cartesian product.

use CROSS JOIN for that

now you have no reason to use comma list syntax at all