Return 7 rows ALWAYS

[The Little Guy]

I have a table that has a column that has the week day (Sunday - Saturday). I have a query, and I ALWAYS want it to return 7 rows (Sunday - Saturday).

 

SELECT COUNT(*) as total, weekDay FROM sts_cal WHERE pid = '1' GROUP BY weekDay

 

if the column total doesn't have anything I want it to say 0, otherwise put the total...

 

Does this make sense?

[Ken2k7]

I think so. What are the results you get back?

[Zane]

LIMIT 7

[The Little Guy]

LIMIT 7 wont work.

 

Currently my table has 4 values in it, all are "Sunday" (today). I want to return "Monday", "Tuesday", etc. but those days are not in there.

 

Right now it returns this:

total    weekDay

4        Sunday

 

 

I would like it to return this:

total    weekDay

4        Sunday

0        Monday

0        Tuesday

0        Wednesday

0        Thursday

0        Friday

0        Saturday

 

does that make better sense? total will change weekDay will not, but there will ALWAYS be those seven thing returned. Possible?

[Ken2k7]

You're trying to select something that's not there?

[The Little Guy]

Yes

[The Little Guy]

So... Is this possible?

[trq]

if the column total doesn't have anything I want it to say 0, otherwise put the total...

 

So write that logic into your loop while your outputting the results. Nothing to do with mysql.

[fenway]

You're trying to select something that's not there?

You just need to join in a derived table that contains one row for each required date.  Either use an integers table to generate it, or just UNION 7 records.

[The Little Guy]

Thanks Fenway!

 

SELECT COUNT(*) as total, 'Sunday' as lbl FROM sts_cal WHERE pid = '{$this->pid}' AND weekDay = 'Sunday'
UNION
SELECT COUNT(*) as total, 'Monday' as lbl FROM sts_cal WHERE pid = '{$this->pid}' AND weekDay = 'Monday'
UNION
SELECT COUNT(*) as total, 'Tuesday' as lbl FROM sts_cal WHERE pid = '{$this->pid}' AND weekDay = 'Tuesday'
UNION
SELECT COUNT(*) as total, 'Wednesday' as lbl FROM sts_cal WHERE pid = '{$this->pid}' AND weekDay = 'Wednesday'
UNION
SELECT COUNT(*) as total, 'Thursday' as lbl FROM sts_cal WHERE pid = '{$this->pid}' AND weekDay = 'Thursday'
UNION
SELECT COUNT(*) as total, 'Friday' as lbl FROM sts_cal WHERE pid = '{$this->pid}' AND weekDay = 'Friday'
UNION
SELECT COUNT(*) as total, 'Saturday' as lbl FROM sts_cal WHERE pid = '{$this->pid}' AND weekDay = 'Saturday'

[fenway]

Not what I meant, but that works too.

[The Little Guy]

What did you mean? Is there a better more efficient way to do this?

[fenway]

Probably not in this case, assuming proper indexes.