Getting 2 counts from 1 table using join?

Databases
1

Hi,

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!


SELECT 
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!

Thanks,

Danny

2

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”?

3

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

4

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)
user_ip
stat_date (date)

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

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

:slight_smile:

6

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!!

7

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.

8

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

9

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

:slight_smile:

10

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.

11

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:

12

Hey,

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’]?

13

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!?

14

i think so, and i think so

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

15

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?

Date
Total_emails
Total_hits

Where dress_Id = what ever id?

16

no, let’s not, because it does

:slight_smile:

17

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

18

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

19

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.

bazz

20

This will get it in 1 row.

SELECT
EXTRACT(YEAR_MONTH FROM stat_date) AS yrmth
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
GROUP
BY yrmth

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

SELECT
t1.yrmth,
SUM(t1.hits) as hits,
SUM(t1.emails) as emails
FROM
(SELECT
EXTRACT(YEAR_MONTH FROM stat_date) AS yrmth
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
GROUP
BY t1.yrmth