[SOLVED] COUNT Statement Help(Again)

[cyberangel]

 

Hi everbody,

 

I posted a question a few days ago, as I wanted to count the records consisting "Very Good" in 3 columns. thanks for the prompt advice. The problem is that the query works strange, it was supposed to count the record "Very Good" in all columns together. What it does right now is that it shows the sum of all very low. The count of "Very Good" in just one column is 40, but if I try to count all 3 columns together, it shows me the sum only "8" records.

Can anybody guide me further please?

 

SELECT column1,column2,column3, COUNT(*) AS total FROM mytable 
WHERE StartDate AND EndDate BETWEEN '2006-12-01' AND '2007-12-31' 
AND column1 = 'Very Good' AND column2 = 'Very Good' 
AND column3 = 'Very Good' GROUP BY column1

 

Thanks in advance

[toplay]

I'm not sure what you're trying to do with the "GROUP BY" on just one column. Anyway, the main thing that I see wrong is your StartDate. The below query assumes you want the start date and end date to be in that same date range:

 

SELECT column1,column2,column3, COUNT(*) AS total FROM mytable 
WHERE StartDate BETWEEN '2006-12-01' AND '2007-12-31' 
AND EndDate BETWEEN '2006-12-01' AND '2007-12-31' 
AND column1 = 'Very Good' 
AND column2 = 'Very Good' 
AND column3 = 'Very Good' 

 

You can't just say "WHERE StartDate AND ..." because it would mean any rows with a value in StartDate would be included.

[cyberangel]

Hi toplay,

 

I tried to group it by all 3 columns but still displays the same result.

I removed the EndDate totally and wrote it this way:

 

SELECT column1,column2,column3, COUNT(*) AS total FROM mytable 
WHERE StartDate '2006-12-01' AND '2007-12-31' 
AND column1 = 'Very Good' AND column2 = 'Very Good' 
AND column3 = 'Very Good' GROUP BY column1
GROUP BY MtbrFrdlt, MtbrKmpt, RezptZmrResrv

 

Is it any other way that I can get the result that I want. I mean it will also be Ok if I could calculate and display the result of multiple queries on the website with PHP or Javascript.

I tried many things in PHP too, but couldn't do it. I don't know how to declare a variable that can be reconised all the way down in PHP. Suppose I count the column1 in the 1st query as "total1" and column2 as "total2" and 3 as "total3" and echo the result of "total1+total2+total3".

 

[toplay]

SELECT column1,column2,column3, COUNT(*) AS total FROM mytable 
WHERE StartDate '2006-12-01' AND '2007-12-31' 
AND column1 = 'Very Good' AND column2 = 'Very Good' 
AND column3 = 'Very Good' GROUP BY column1
GROUP BY MtbrFrdlt, MtbrKmpt, RezptZmrResrv

 

Your last query shown now has two GROUP BY'S in it.  Why do you need the GROUP BY in the first place? I'm really getting confused on what you want.

 

Basically the query I showed and yours counts how many rows that all have "Very Good" in all three columns. Maybe that's not what you're intending, and you should use "OR" instead of "AND".

 

This returns a count of how many rows no matter whether column1, 2 or 3 has "Very Good":

SELECT column1,column2,column3, COUNT(*) AS total FROM mytable 
WHERE 
(StartDate BETWEEN '2006-12-01' AND '2007-12-31')
AND
(column1 = 'Very Good' OR column2 = 'Very Good' OR column3 = 'Very Good')

 

This query will count how many "Very Good" in each individual column based on start date:

SELECT 
             SUM(IF(column1 = 'Very Good', 1, 0)) AS col1_total
          ,  SUM(IF(column2 = 'Very Good', 1, 0)) AS col2_total
          ,  SUM(IF(column3 = 'Very Good', 1, 0)) AS col3_total

FROM mytable 
WHERE StartDate BETWEEN '2006-12-01' AND '2007-12-31'

 

They in PHP, you would simply add them up to get the grand total.

 

 

[cyberangel]

 

Thanks again toplay,

 

you had been very helpfull and I know my question and code was pretty much confusing.

 

Your third code with :

SUM(IF(column1..............

 

is exactly what I needed, it shows the exact results. If you could help me modify it so that it displays the sum of (col1_total, col2_total and col3_total) instead of displaying the count of all the three columns in three cells, it would be great.

For example now it shows:

 

col1_total  col2_total  col3_total

    40              30              10

 

It would be great if it would say for example:

 

          Total

            80

 

Many Thanks in advance

[toplay]

Here are two ways to go.

 

1) As stated already, simply use PHP to add the three column values together.

 

2) If you don't care about individual column totals, then change the query to something like:

 

SELECT 
            col1_total + col2_total + col3_total AS grand_total
  FROM
           (
             SELECT 
                           SUM(IF(column1 = 'Very Good', 1, 0)) AS col1_total
                        ,  SUM(IF(column2 = 'Very Good', 1, 0)) AS col2_total
                        ,  SUM(IF(column3 = 'Very Good', 1, 0)) AS col3_total

                FROM mytable  
                WHERE StartDate BETWEEN '2006-12-01' AND '2007-12-31'
           ) AS t
;

[cyberangel]

 

 

Thank You Very Very Much toplay.

 

You solved the whole problem