Advanced SQL - Multiple Count statements in one select

[whitsey]

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?

[Otto Carl Marte]

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

[ChrisC]

On May 30, 10:00 am, Otto Carl Marte <[e-mail]:

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

[ChrisC]

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

[whitsey]

On May 31, 9:20 am, ChrisC <[e-mail]:

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?

[ChrisC]

On May 30, 6:50 pm, whitsey <[e-mail]:

On May 31, 9:20 am, ChrisC <[e-mail]:

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