469,353 Members | 2,053 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,353 developers. It's quick & easy.

Advanced SQL - Multiple Count statements in one select

Here is what I have:

SELECT
(SELECT
COUNT(*) AS SEARCHES
FROM
SEARCHES
INNER JOIN GROUPS ON
SEARCHES.SITE_ID = GROUPS.SITE_ID
WHERE
(GROUPS.GROUP_ID = 6)) AS "SEARCHES",
(SELECT
COUNT(*)
FROM
GROUPS
INNER JOIN SEARCHES
INNER JOIN ORDERS ON
SEARCHES.SEARCH_ID = ORDERS.SEARCH_ID ON
SEARCHES.SITE_ID = GROUPS.SITE_ID
WHERE
(GROUPS.GROUP_ID = 6)) AS "ORDERS"
FROM GROUPS, SEARCHES, ORDERS

each inner select statement returns results when executed on their own
however when combined into a single select, I get nothing!

Also, how do I break the results down by months?

May 30 '07 #1
5 17850
HOw about:

SELECT
(SELECT
COUNT(*)
FROM
SEARCHES
INNER JOIN GROUPS ON
SEARCHES.SITE_ID = GROUPS.SITE_ID
WHERE
(GROUPS.GROUP_ID = 6)) AS "SEARCHES",
(SELECT
COUNT(*)
FROM
GROUPS
INNER JOIN SEARCHES
INNER JOIN ORDERS ON
SEARCHES.SEARCH_ID = ORDERS.SEARCH_ID ON
SEARCHES.SITE_ID = GROUPS.SITE_ID
WHERE
(GROUPS.GROUP_ID = 6)) AS "ORDERS"
FROM SYSIBM.SYSDUMMY1

May 30 '07 #2
On May 30, 10:00 am, Otto Carl Marte <Otto.Ma...@gmail.comwrote:
HOw about:

SELECT
(SELECT
COUNT(*)
FROM
SEARCHES
INNER JOIN GROUPS ON
SEARCHES.SITE_ID = GROUPS.SITE_ID
WHERE
(GROUPS.GROUP_ID = 6)) AS "SEARCHES",
(SELECT
COUNT(*)
FROM
GROUPS
INNER JOIN SEARCHES
INNER JOIN ORDERS ON
SEARCHES.SEARCH_ID = ORDERS.SEARCH_ID ON
SEARCHES.SITE_ID = GROUPS.SITE_ID
WHERE
(GROUPS.GROUP_ID = 6)) AS "ORDERS"
FROM SYSIBM.SYSDUMMY1
For month break down, try:
SELECT s.MONTH, s.SEARCHES, o.ORDERS from
(SELECT
COUNT(*) as SEARCHES
FROM
SEARCHES
INNER JOIN GROUPS ON
SEARCHES.SITE_ID = GROUPS.SITE_ID
WHERE
(GROUPS.GROUP_ID = 6)) AS "SEARCHES",
(SELECT
COUNT(*)
FROM
GROUPS
INNER JOIN SEARCHES
INNER JOIN ORDERS ON
SEARCHES.SEARCH_ID = ORDERS.SEARCH_ID ON
SEARCHES.SITE_ID = GROUPS.SITE_ID
WHERE
(GROUPS.GROUP_ID = 6)) AS "ORDERS"
FROM SYSIBM.SYSDUMMY1
MONTH(Searches.Search_Date

May 30 '07 #3
Sorry, hit the wrong button last time.

Try:
SELECT s.MONTH, s.SEARCHES, o.ORDERS
from (SELECT searches.search_date // 100 as MONTH,
COUNT(*) as SEARCHES
FROM
SEARCHES
INNER JOIN GROUPS ON
SEARCHES.SITE_ID = GROUPS.SITE_ID
WHERE
GROUPS.GROUP_ID = 6
GROUP BY searches.search_Date // 100) s
full outer join (SELECT searches.search_date // 100 as MONTH,
COUNT(*) as ORDERS
FROM
GROUPS
INNER JOIN SEARCHES
INNER JOIN ORDERS ON
SEARCHES.SEARCH_ID = ORDERS.SEARCH_ID ON
SEARCHES.SITE_ID = GROUPS.SITE_ID
WHERE
GROUPS.GROUP_ID = 6
GROUP BY searches.search_Date // 100) o
ON s.MONTH = o.MONTH

This should get to the amounts broken down by month (well, year and
month). If you truely want them by month (and January from this year
and last year summed up together), then you can substitute
MONTH(searches.search_date)
for
searches.search_date // 100

-Chris

May 30 '07 #4
On May 31, 9:20 am, ChrisC <cunningham...@gmail.comwrote:
Sorry, hit the wrong button last time.

Try:
SELECT s.MONTH, s.SEARCHES, o.ORDERS
from (SELECT searches.search_date // 100 as MONTH,
COUNT(*) as SEARCHES
FROM
SEARCHES
INNER JOIN GROUPS ON
SEARCHES.SITE_ID = GROUPS.SITE_ID
WHERE
GROUPS.GROUP_ID = 6
GROUP BY searches.search_Date // 100) s
full outer join (SELECT searches.search_date // 100 as MONTH,
COUNT(*) as ORDERS
FROM
GROUPS
INNER JOIN SEARCHES
INNER JOIN ORDERS ON
SEARCHES.SEARCH_ID = ORDERS.SEARCH_ID ON
SEARCHES.SITE_ID = GROUPS.SITE_ID
WHERE
GROUPS.GROUP_ID = 6
GROUP BY searches.search_Date // 100) o
ON s.MONTH = o.MONTH

This should get to the amounts broken down by month (well, year and
month). If you truely want them by month (and January from this year
and last year summed up together), then you can substitute
MONTH(searches.search_date)
for
searches.search_date // 100

-Chris
Thanks Chris, that is exactly what I was looking for however my dates
are stored as TIMESTAMP and when I attempt to perform the arithmetic
conversion I get an error stating that one of the operands is not
numeric. I'm assuming that the TIMESTAMP can't be converted to a
number? I'm searching for a solution but can't really find
anything...

Do you have any other suggestions?

May 31 '07 #5
On May 30, 6:50 pm, whitsey <lysterfiel...@gmail.comwrote:
On May 31, 9:20 am, ChrisC <cunningham...@gmail.comwrote:


Sorry, hit the wrong button last time.
Try:
SELECT s.MONTH, s.SEARCHES, o.ORDERS
from (SELECT searches.search_date // 100 as MONTH,
COUNT(*) as SEARCHES
FROM
SEARCHES
INNER JOIN GROUPS ON
SEARCHES.SITE_ID = GROUPS.SITE_ID
WHERE
GROUPS.GROUP_ID = 6
GROUP BY searches.search_Date // 100) s
full outer join (SELECT searches.search_date // 100 as MONTH,
COUNT(*) as ORDERS
FROM
GROUPS
INNER JOIN SEARCHES
INNER JOIN ORDERS ON
SEARCHES.SEARCH_ID = ORDERS.SEARCH_ID ON
SEARCHES.SITE_ID = GROUPS.SITE_ID
WHERE
GROUPS.GROUP_ID = 6
GROUP BY searches.search_Date // 100) o
ON s.MONTH = o.MONTH
This should get to the amounts broken down by month (well, year and
month). If you truely want them by month (and January from this year
and last year summed up together), then you can substitute
MONTH(searches.search_date)
for
searches.search_date // 100
-Chris

Thanks Chris, that is exactly what I was looking for however my dates
are stored as TIMESTAMP and when I attempt to perform the arithmetic
conversion I get an error stating that one of the operands is not
numeric. I'm assuming that the TIMESTAMP can't be converted to a
number? I'm searching for a solution but can't really find
anything...

Do you have any other suggestions?- Hide quoted text -

- Show quoted text -
To convert the timestamp into a date (sothtat you can turn it into an
integer), you need to wrap it with a DATE() function. In other words,
replace searches.search_date with date(searches.search_date), or even
INTEGER(date(searches.search_date)).

-Chris

Jun 1 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Ben Hallert | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.