select statement to count several columns with where clauses

[adishardis]

I have a answer table and a attribute table.

Answers table:
-----token-------question1a1-------question1a2-------question2------........
--aaaBBB324Cc---------Y----------------NULL--------------Y-----........

attribute table:
--------token---------attribute_1------....
------aaaBBB324Cc-----2012-08-13-------.....

With higcharts I am trying to display a graph with the total count for each question1(a1-a7) from a month back, with dates from the attribute table (namely the attribute_1 column)

this is not getting the desired results but here's my last attempt of maybe a hundred :)

$trackingquery1 = mysql_query("SELECT count(a.262697X1X1a1) as annonser, 
                                        count(a.262697X1X1a2) as adresserat, 
                                        UNIX_TIMESTAMP(t.attribute_1) as datum 
                                  FROM lime_survey_262697 a, attributetable t 
                                  WHERE a.262697X1X1a1 = 'Y' and a.262697X1X1a2 = 'Y' and t.attribute_1 > DATE_SUB(NOW(), INTERVAL 1 MONTH) group by(t.attribute_1) ORDER BY t.attribute_1 ASC");
    if ($trackingquery1) {  
    while ($row1 = mysql_fetch_array($trackingquery1)) { 
        $dates .= "\n\t'". date("d-M-Y", strtotime($row1['datum'])) ."', ";
            $annons .= "\n\t ".$row1['annonser'].",";
            $addreserad  .= "\n\t ".$row1['adresserat'].",";
            }

I've hit a brick wall here and I've really tried to make it work but I don't know what to do now... :-/

Take care
Adishardis

[LastMitch]

With higcharts I am trying to display a graph with the total count for each question1(a1-a7) from a month back, with dates from the attribute table (namely the attribute_1 column)

Explain to me what is this: a.262697X1X1a1 ?

Not the table but the token what does the number represent?

[adishardis]

Thanks for your reply!

I'm using limesurvey to gather info and in the answers table all the field names are like 262697X1X1a1 where 262697 is the survey id, and the first x1 is the questiongroup id, second x1 is question id and finally a1 is the first "answeroption" of that question.

I should have written the answerstable like this:
-----token-------262697X1X1a1-------262697X1X1a2-------262697X1X1a3------........
--aaaBBB324Cc---------Y-----------------------NULL--------------------Y-----........

If I skip the datefield from the Attribute table and use a date from the answers table the query looks like this:

$trackingquery = mysql_query("SELECT *, SUM(CASE WHEN 262697X1X1a1 IN ('Y') THEN 1 ELSE 0 END) AS annons, SUM(CASE WHEN 262697X1X1a2 IN ('Y') THEN 1 ELSE 0 END) AS addreserad, datestamp as datums FROM lime_survey_262697 where datestamp > DATE_SUB(NOW(), INTERVAL 1 MONTH) group by(datestamp) ORDER BY datestamp ASC");
    if ($trackingquery) {
    while ($row = mysql_fetch_array($trackingquery)) { 
        $dates .= "\n\t'". date("d-M-Y", strtotime($row['datums'])) ."', ";
            $annons .= "\n\t ". $row['annons'] .",";
            $addreserad  .= "\n\t ". $row['addreserad'] .",";

But what I really need is instead of using the datestamp from answers table, I want to use the datefield (attribute_1) from the attribute table.

So I'm thinking that I have to join the to tables on their common token field to be able to get the right date!?

Hope this makes it clearer what I'm trying to achieve! :)

Cheers
/Adam