Hi All,
I have a column called fb (facebook) with possible values y, n, m (yes no maybe) and a date column.
For each date I want to get a total count of the y, n, m
What I have is,
(fbli = facebook login, phli is phone login)
SELECT ttl.fb AS tttl
,COUNT(fbli.fb) AS fbli
,COUNT(phli.fb) AS phli
,DATE(ttl.date) AS date
FROM fbdata AS ttl
LEFT JOIN fbdata AS fbli
ON ttl.fb = 'y'
LEFT JOIN fbdata AS phli
ON ttl.fb = 'n'
WHERE ttl.nasid = :wttlnasid1 AND DATE(ttl.Date) BETWEEN :wbegin AND :wend
GROUP BY DATE(ttl.date)
The query takes forever to run and the values are not correct.
I saw an example in stackoverflow that had parentheses around the ON condition, it didn’t work
If I change ttl.date to phli.date it executes for over 30 seconds
If I use DISTINCT in the count, everything is 3.
I have tried throwing everything at this query.
You’re not getting the results you’re looking for because those joins are bogus - you’re basically getting a complete count of all records on the fbdata table for each one as you’re using a LEFT JOIN and not giving any criteria to limit the values on those joined tables, so it’ll pick each record.
All you need is this query.
SELECT DATE([date]) AS date
, fb AS ttl
, count(fb) as responseCount
FROM fbdata
WHERE nasid = :wttlnasid1
AND DATE([date]) BETWEEN :wbegin AND :wend
AND fb in ('y', 'n')
GROUP BY DATE([date]), fb
You’ll get a result something like this:
[TABLE=“class: grid, width: 500”]
[TR]
[TD]Date[/TD]
[TD]fb[/TD]
[TD]responseCount[/TD]
[/TR]
[TR]
[TD]1-Feb-2014[/TD]
[TD]n[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1-Feb-2014[/TD]
[TD]y[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]2-Feb-2014[/TD]
[TD]n[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]3-Feb-2014[/TD]
[TD]y[/TD]
[TD]100[/TD]
[/TR]
[/TABLE]
If you REALLY want to get it all on one record per date, you could do something like this (not tested but should be close), but the performance may not be worth the processing time depending on how large you table is and how well your table is indexed.
SELECT DATE(d.[date]) AS date
, count(y.[date]) as yesCount
, count(n.[date]) as noCount
FROM fbdata d
JOIN fbdata y ON d.nasid = y.nasid and d.[date] = y.[date] AND y.fb = 'y'
JOIN fbdata n ON d.nasid = y.nasid and d.[date] = y.[date] AND n.fb = 'n'
WHERE nasid = :wttlnasid1
AND DATE(d.[date]) BETWEEN :wbegin AND :wend
GROUP BY DATE(d.[date])
Sorry for the long delay in my reply but had a shift of priorities.
I tried your query, the second one and got extremely high numbers
This is the query, I used d.nasid in the where clause
SELECT DATE(d.date) AS DATE
,COUNT(y.date) AS yesCount
,COUNT(n.date) AS noCount
FROM fbdata d
JOIN fbdata y ON d.nasid = y.nasid AND DATE(d.date) = DATE(y.date) AND y.fb = 'y'
JOIN fbdata n ON d.nasid = y.nasid AND DATE(d.date) = DATE(y.date) AND n.fb = 'n'
WHERE d.nasid = :wdnasid1 AND DATE(d.date) BETWEEN :wbegin AND :wend GROUP BY DATE(d.date)
I really appreciate the help, I did get your first query working and will play with that some more.
It looks the the second query is what I’m after though, did I do something wrong?.
joins are not required nor advised in this problem
SELECT DATE(`Date`) AS `Date`
, COUNT(*) AS tttl
, COUNT(CASE WHEN fb = 'y' THEN fb ELSE NULL END) AS 'y'
, COUNT(CASE WHEN fb = 'n' THEN fb ELSE NULL END) AS 'n'
, COUNT(CASE WHEN fb = 'm' THEN fb ELSE NULL END) AS 'm'
FROM fbdata
WHERE `Date` >= :wbegin
AND `Date` < :wend + INTERVAL 1 DAY
GROUP
BY DATE(`Date`)
note the WHERE condition for the datetimes – this may optimize the performance if there’s an index on the Date column (that’s a poor name, by the way)
Rudy, you are brilliant.
I bow to you. Your solution is so elegantly simple and worked right out of the box.
On a side note, reserved keywords have gotten me many times, I can’t believe that select date didn’t throw an error.
Page 285 of your book has DATE as a keyword.
Thanks soo much.
I have only written one query with CASE and didn’t even (think of) consider it.
So many thanks,
Loren