Getting 2 counts from 1 table using join?


Im trying to get stats from a table. It holds hits and emails sent. This is what i tried, but its not correct:

Im almost certain the “Dress” table doesnt need to be in there. Only added it thinking it would help!

DATE_FORMAT(hits.stat_date, '%D %b') as date,
COUNT(hits.stat_id) AS total_hits,
COUNT(emails.stat_id) AS total_emails
FROM dress_listing as dress
LEFT JOIN listing_stats as hits ON dress.dress_id = hits.dress_id and hits.stat_type = 'hit'
LEFT JOIN listing_stats as emails ON dress.dress_id = emails.dress_id and emails.stat_type = 'email'
GROUP BY DATE_FORMAT(hits.stat_date, '%Y-%m-%d')

Any suggestions would be appreciated!



you’re not trying to get stats from a table, you’re trying to get stats from three tables

what the heck is a dress?

and how are your tables related? which is the “one” and which is the “many”?

it’s what I wear on fridays! lol (sorry, couldn’t resist).

A dress is an article of clothing typically worn by women. :op

The site has irish dance dress listings for sale by sellers!

And the data i need is from the stats table. I only added the listing table thinking it would make it easier to get the counts by using left join. But didnt work.

My stats table looks like this:

stat_id (int)
dress_id (int)
stat_type (varchar) (can be hit or email)
stat_date (date)

i want to get total_hits and total_emails grouped by month where dress_id = what ever ID is set.

SELECT stat_type
     , EXTRACT(YEAR_MONTH FROM stat_date) AS yrmth
     , COUNT(*)
  FROM stats
 WHERE dress_id = 937
    BY stat_type
     , yrmth


Thank you! It gets the right info, but now i need to be able to display it like:

date | hits | emails

1st Jan | 10 | 0
2nd Jan | 30 | 2
3rd Jan | 50 | 10

Currently i cant do that with the code. It only returns 2 rows with the stat type and total. But heading in the right direction!!

Not that I doubt a rudy query for 1 second but, from curiosity, what do you get without the group by clause? It might highlight that your data isn’t as you think.

you might perhaps ask yourself why you can’t :slight_smile:

the job of the database is to perform data retrieval as expeditiously as possible

it is the job of the front-end application language to do fancy formatting, including things like pivoting query results from rows into columns

bazz, his data is fine, his table is fine, and my query is fine


OK. It was this that made me wonder about the data

It only returns 2 rows with the stat type and total.

glad its sorted.

yes, there are only two rows of totals – one for each stat_type…

… multiplied by however many months there are, which i expect is only one, as he appears to have only gotten started :wink:


The site is actually live right now and is recording stats but I couldn’t figure out how to get both totals!

I was tring to have a statement where I could call each total seperatly to create graphs.

Would i put the data into an array? Or is there a way to call each total using $row[‘total_emails’] and $row[‘total_hits’]?

With the current select statement, even if I had 6 months of data. It would produce 2 rows per month in my do while code. 12 rows instead of 6!?

i think so, and i think so

sorry, i don’t do perl or whatever that is :wink:

Ok. Let’s say “hypothetically” that php has no way of displaying the information obtained from your mysql statement.

Is it at all possible, to return the following values (in 1 row) and if so, how?


Where dress_Id = what ever id?

no, let’s not, because it does


Would I do a “while loop” putting them into an array and then do a “for each” loop of the array?

i wish i could help you, i really do, but i don’t do php

however, i do know that it is trivial code, because i’ve seen it lots of times, and yes, i think it involves arrays

You’ll get a better and quicker response if you post in the php forum. I don’t do php either (I do perl) so I ain’t any help either, with this one.


This will get it in 1 row.

SUM(CASE WHEN stat_type = ‘hit’ THEN 1 ELSE 0 END) as hits,
SUM(CASE WHEN stat_type = ‘email’ THEN 1 ELSE 0 END) as emails
FROM stats
WHERE dress_id = 937
BY yrmth

If your db does not allow CASE within an aggregate function, you could alternately structure it like this:

SUM(t1.hits) as hits,
SUM(t1.emails) as emails
CASE WHEN stat_type = ‘hit’ THEN 1 ELSE 0 END as hits,
CASE WHEN stat_type = ‘email’ THEN 1 ELSE 0 END as emails
FROM stats
WHERE dress_id = 937) t1
BY t1.yrmth