StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSELECT inside SELECT statement returns 0 count
primarykey
Id
18329769
data
AcceptedAnswerId
18330573
AnswerCount
2
ClosedDate
CommentCount
3
CommunityOwnedDate
CreationDate
2013-08-20T07:57:05.620
FavoriteCount
1
LastActivityDate
2014-05-22T05:54:09.960
LastEditDate
LastEditorUserId
0
OwnerUserId
2456759
ParentId
0
PostTypeId
1
Score
1
ViewCount
279
LastEditorDisplayName
text
Body
I have this calendar where there is a list of events in every day and I use this query to count the events in a particular date. In my database I have <code>start_time</code> and <code>end_time</code> field where the user have a schedule of materials to be specific. I tried to <code>select</code> the events in that day but it seems there's something wrong in my query. Because the user can borrow more than one material so it will be stored in the database with the same <code>start_time</code> and <code>end_time</code> as well. What I want is to count all the data of the user with the same <code>start_time</code> and I tried <code>GROUP BY</code> but it seems doesn't work either. Here is my database: <pre><code> ----Table: schedule---- id | materialID | borrowerID | date_reserve | start_time | end_time| ----------------------------------------------------------------------------------- 9 | 7 | bobi | 2013-08-16 | 07:01:12 | 07:01:12| 10 | 10 | bobi | 2013-08-16 | 07:01:12 | 07:01:12| 11 | 12 | bobi | 2013-08-16 | 07:01:12 | 07:01:12| 12 | 7 | sobi | 2013-08-18 | 07:01:12 | 07:01:12| ------------------------------------------------------------------------------------ </code></pre> Here is my query: <pre><code> $cal_data = array(); for($i=1;$i<=31;$i++) { $date = "$year-$month-$i"; $this->db->select('(SELECT COUNT(id) as count_s FROM schedule WHERE date_reserve='.$date.' GROUP BY start_time) as count', FALSE); $this->db->select('date_reserve,borrowerID,start_time,end_time'); $this->db->from('schedule'); $this->db->where('date_reserve',"$year-$month-$i"); $this->db->group_by('start_time'); $query = $this->db->get(); foreach ($query->result() as $row) { $cal_data[$i] = ($row->count > 0) ? $row->count.' ' .'event(s)' : ''; } } </code></pre> So the expected output with the will be: <pre><code> count | date_reserve | borrowerID | start_time | end_time --------------------------------------------------------------------------------------------------- 1 | 2013-08-16 | bobi | 07:01:12 | 07:01:12 </code></pre> In here there's a big BUT where in that query it will give you this output. Note: <code>I'm using CodeIgniter</code>. I used <code>$this->output->enable_profiler(TRUE);</code> and try the date <code>2013-08-16</code>(because it is multiple selection) to MySQL on my server and give me this. <pre><code> count | date_reserve | borrowerID | start_time | end_time --------------------------------------------------------------------------------------------------- NULL | 2013-08-16 | bobi | 07:01:12 | 07:01:12 </code></pre> So what do you think the solution for this?
Tags
<mysql><sql><select><group-by>
Title
SELECT inside SELECT statement returns 0 count
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USleonardeveloper
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POSELECT inside SELECT statement returns 0 count
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 POSELECT inside SELECT statement returns 0 count
 UserUserId
 USleonardeveloper
 VoteTypeVoteTypeId
 VTFavorite
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.