Mutiple Where Statment Query

[_spaz]

Can someone help me with the syntax with the below mutiple where statement query? Can't seem to figure it out or if its even possible....  Any help would be great!  Thanks!

 

Calculate the movies Mins by week:

 

SELECT week(Release) as week,

sum(Mins where Service LIKE 'Movies') as Movies_total,

sum(Mins where Service LIKE 'FREE') as FREE_total,

sum(Mins where Service LIKE 'Regular') as REGULAR_total

FROM Table

where Release >= '2009-12-06' and Release <= '2009-12-29'

group by week

[kickstart]

Hi

 

Try this:-

 

SELECT week(`Release`) as weekno,
sum(Mins)
FROM Table1
where `Release` BETWEEN '2009-12-06' AND '2009-12-29'
AND Service IN ('Movies','FREE', 'Regular')
group by weekno, Service

 

Just brings them back as 3 seperate rows.

 

However I would avoid using MySQL reserved words for column names, etc (ie Release and Week).

 

All the best

 

Keith

[_spaz]

Hi

 

Try this:-

 

SELECT week(`Release`) as weekno,
sum(Mins)
FROM Table1
where `Release` BETWEEN '2009-12-06' AND '2009-12-29'
AND Service IN ('Movies','FREE', 'Regular')
group by weekno, Service

 

Just brings them back as 3 seperate rows.

 

However I would avoid using MySQL reserved words for column names, etc (ie Release and Week).

 

All the best

 

Keith

 

This appears to give me a tally for all three combined,  i require each 'type' to be seperate so there's a total for FREE, REGULAR, MOVIES in different columns.  Sorry if I wasn't clear.....

[kickstart]

Hi

 

It shouldn't do, and doesn't when I have just knocked up a table and tested it.

 

Is a minor fault though as the column "Service" should be specified in the SELECT list.

 

All the best

 

Keith

[_spaz]

Hi

 

It shouldn't do, and doesn't when I have just knocked up a table and tested it.

 

Is a minor fault though as the column "Service" should be specified in the SELECT list.

 

All the best

 

Keith

 

Great, is there a way to make it so that it displays it in Columns instead of rows?

 

Weekno  Movies  FREE  Regular

48            564      444    23423

49

50

51

[kickstart]

Hi

 

Bit of playing around and think this would do it. However not sure how efficient it would be:-

 

SELECT Week( `Release` ) , 
SUM( IF( `Service` = 'Movies', `Mins` , NULL ) ) AS ServiceCount, 
SUM( IF( `Service` = 'FREE', `Mins` , NULL ) ) AS FreeCount, 
SUM( IF( `Service` = 'Regular', `Mins` , NULL ) ) AS RegularCount
FROM Table1
GROUP BY Week( `Release` )

 

All the best

 

Keith

[_spaz]

Hi

 

Bit of playing around and think this would do it. However not sure how efficient it would be:-

 

SELECT Week( `Release` ) , 
SUM( IF( `Service` = 'Movies', `Mins` , NULL ) ) AS ServiceCount, 
SUM( IF( `Service` = 'FREE', `Mins` , NULL ) ) AS FreeCount, 
SUM( IF( `Service` = 'Regular', `Mins` , NULL ) ) AS RegularCount
FROM Table1
GROUP BY Week( `Release` )

 

All the best

 

Keith

 

 

Works Great.... Thanks for you're help!!