# Combined SQL Query

**URL:** <https://forum.xojo.com/t/combined-sql-query/19367>\
**Category:** Databases\
**Created:** [September 20, 2014, 11:22am UTC](https://forum.xojo.com/t/combined-sql-query/19367 "2014-09-20T11:22:13Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Paul\_Budd](https://forum.xojo.com/user_avatar/forum.xojo.com/paul_budd/32/799_2.png) [@Paul\_Budd](https://forum.xojo.com/u/Paul_Budd)\
**Post date:** [September 20, 2014, 11:22am UTC](https://forum.xojo.com/t/combined-sql-query/19367/1 "2014-09-20T11:22:13Z")

</div>

Is there a way of combing some additional sql into this query.

Each of the ‘guides’ can attend multiple sessions on a particular day. I want to count the number of unique days (which the query does not do), and also the total number of sessions for each guide overall, which the query below does do.

`SELECT
Count(sessions.Event_Date),
sessions_link.Guide_Name,
guide_details.Gift_Aid,
guide_details.Post_Code
FROM
guide_details
INNER JOIN sessions_link ON sessions_link.ID_Guide = guide_details.ID_Guide
INNER JOIN sessions ON sessions_link.Session_ID = sessions.ID_Event
WHERE
guide_details.Gift_Aid = '1' AND
sessions.Event_Date BETWEEN '2014-01-01' AND '2014-12-31'
GROUP BY
sessions_link.Guide_Name,
guide_details.Gift_Aid,
guide_details.Post_Code
`

This give me for example  
31 Ceri 1 BS9  
42 David 1 BS32  
34 Mary 1 BS16

Another query ‘splits up’ the sessions, but for example I want the answer ‘17’ for the number of days as ‘Ceri’ did 17 unique days, on 14 days she did a second session.

`
SELECT
guide_details.Gift_Aid,
Count(sessions.Event_Date),
sessions_link.Guide_Name,
guide_details.Post_Code,
sessions.Event_Session
FROM
guide_details
INNER JOIN sessions_link ON sessions_link.ID_Guide = guide_details.ID_Guide
INNER JOIN sessions ON sessions_link.Session_ID = sessions.ID_Event
WHERE
guide_details.Gift_Aid = '1' AND
sessions.Event_Date BETWEEN '2014-01-01' AND '2014-12-31'
GROUP BY
sessions_link.Guide_Name,
guide_details.Gift_Aid,
guide_details.Post_Code,
sessions.Event_Session`

This gives me  
1 17 Ceri BS9 10  
1 14 Ceri BS9 13  
1 21 David BS16 10  
1 21 David BS16 13  
1 18 Mary BS32 10  
1 16 Mary BS32 13

From the above I would require for example;

1 17 Ceri BS9  
1 21 David BS9  
1 18 Mary BS32

Any pointers in the right direction in combining (if possible) the queries would be helpful.

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![Rich\_H](https://forum.xojo.com/user_avatar/forum.xojo.com/rich_h/32/18188_2.png) [@Rich\_H](https://forum.xojo.com/u/Rich_H)\
**Post date:** [September 20, 2014, 12:05pm UTC](https://forum.xojo.com/t/combined-sql-query/19367/2 "2014-09-20T12:05:26Z")

</div>

off topic to your initial question, but wanted to share 🙂

for this code:

```auto
sessions.Event_Date BETWEEN '2014-01-01' AND '2014-12-31'
```

you should change to:

```auto
sessions.Event_Date BETWEEN '2014-01-01' AND '2014-12-31 23:59:59'
```

the reason being is that in your end date, you are not specifying time stamp in which the system by default will assign 00:00:00. When this happens, you are omitting the data that occurred on 2014-12-31.

if you did not want to supply time stamp, you could also change the end date to 2015-01-01 which would be translated as 2015-01-01 00:00:00

---

<div class="post-metadata">

**Author:** ![Paul\_Budd](https://forum.xojo.com/user_avatar/forum.xojo.com/paul_budd/32/799_2.png) [@Paul\_Budd](https://forum.xojo.com/u/Paul_Budd)\
**Post date:** [September 20, 2014, 12:27pm UTC](https://forum.xojo.com/t/combined-sql-query/19367/3 "2014-09-20T12:27:23Z")

</div>

Rich, I am a little confused by that as when I just run the query as I have it (in Navicat) I get the 4 records that are on the last date specified as I would expect so it appears to be working as it is.

My data source is MySQL and the Event\_Date field is a ‘date field’ as opposed to timestamp.

---

<div class="post-metadata">

**Author:** ![Rich\_H](https://forum.xojo.com/user_avatar/forum.xojo.com/rich_h/32/18188_2.png) [@Rich\_H](https://forum.xojo.com/u/Rich_H)\
**Post date:** [September 20, 2014, 1:36pm UTC](https://forum.xojo.com/t/combined-sql-query/19367/4 "2014-09-20T13:36:59Z")

</div>

I am using MS SQL for this, but I believe this would carry over to MYSQL.

lets create a temp table of 500 rows and add a date stamp with an increment of 1 hour per row.

[code]create table #T\_TMP\_1 (r\_id int, r\_date datetime)

declare @startInt int, @endInt int  
set @startInt = 1  
set @endInt = 500

while (@startInt \<= @endInt)  
begin  
insert into #T\_TMP\_1(r\_id, r\_date) values (@startInt, DATEADD(hour, @startInt, getdate()))  
set @startInt = @startInt + 1  
end[/code]

Now, if we get the newest temp table, it would be 2014-11-11

if we run the following query:

`select CONVERT(char(8), r_date, 112), COUNT(1) [row_count]
from #T_TMP_1
where r_date between '2014-01-01' and '2014-10-11'
group by CONVERT(char(8), r_date, 112)
order by 1 desc
`

you will see that the newest date is 2014-11-10

but if we run this code:

`select CONVERT(char(8), r_date, 112), COUNT(1) [row_count]
from #T_TMP_1
where r_date between '2014-01-01' and '2014-10-11 23:59:59'
group by CONVERT(char(8), r_date, 112)
order by 1 desc`

newest date is 2014-11-11 with the group by counts.

---

<div class="post-metadata">

**Author:** ![Rich\_H](https://forum.xojo.com/user_avatar/forum.xojo.com/rich_h/32/18188_2.png) [@Rich\_H](https://forum.xojo.com/u/Rich_H)\
**Post date:** [September 20, 2014, 1:40pm UTC](https://forum.xojo.com/t/combined-sql-query/19367/5 "2014-09-20T13:40:54Z")

</div>

grrr… very annoying I cant edit.

newest date should be 2014-10-11 and not 2014-11-11

---

<div class="post-metadata">

**Author:** ![Rich\_H](https://forum.xojo.com/user_avatar/forum.xojo.com/rich_h/32/18188_2.png) [@Rich\_H](https://forum.xojo.com/u/Rich_H)\
**Post date:** [September 20, 2014, 1:48pm UTC](https://forum.xojo.com/t/combined-sql-query/19367/6 "2014-09-20T13:48:05Z")

</div>

ok… I understand what is happening.

because you are only inserting date, the system would assign 00:00:00 for time. The last date would appear because of the 00:00:00

if you ever did add the time value… you would need to add the 23:59:59 to the end date of the between statement.

---

<div class="post-metadata">

**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\
**Post date:** [October 29, 2020, 5:25pm UTC](https://forum.xojo.com/t/combined-sql-query/19367/7 "2020-10-29T17:25:25Z")

</div>