Advanced SQL - Extracting values by months

[whitsey]

Help!

I am trying to write an SQL statement (without much luck) to extract
the total number of searches and orders from a particular customer,
grouped into days/months/weeks (depending on the report type.
The tables are as follows:
Searches
--------------
search_id (PKEY)
customer_id (FKEY)
product_id
cost
date
.....
Orders
-----------
order_id (PKEY)
search_id (FKEY)
order_value
date
....
Groups
-----------
group_id (PKEY)
customer_id
....
Customers
---------------
customer_id (PKEY)
.....
What I want to achieve from this is
For all customers with Group_id = '17'
Mth 1 1244(Searches) 846(Orders)
Mth 2 2345(Searches) 1834(Orders)
Mth 3 2342(Searches) 1954(Orders)
Mth 4 2425(Searches) 2134(Orders)
Mth 5 5235(Searches) 4253(Orders)
I am clearly nowhere near the level I need to be to achieve this.
Can it be achieved in a single statement?
Thanks

[Serge Rielau]

whitsey wrote:

Help!

I am trying to write an SQL statement (without much luck) to extract
the total number of searches and orders from a particular customer,
grouped into days/months/weeks (depending on the report type.
The tables are as follows:
Searches
--------------
search_id (PKEY)
customer_id (FKEY)
product_id
cost
date
....
Orders
-----------
order_id (PKEY)
search_id (FKEY)
order_value
date
...
Groups
-----------
group_id (PKEY)
customer_id
...
Customers
---------------
customer_id (PKEY)
....
What I want to achieve from this is
For all customers with Group_id = '17'
Mth 1 1244(Searches) 846(Orders)
Mth 2 2345(Searches) 1834(Orders)
Mth 3 2342(Searches) 1954(Orders)
Mth 4 2425(Searches) 2134(Orders)
Mth 5 5235(Searches) 4253(Orders)
I am clearly nowhere near the level I need to be to achieve this.
Can it be achieved in a single statement?

Yes, a join combined with GROUP BY. Use MONTH(date) to coarsify to the
month.
Why don't you post your best shot and we go from there?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[whitsey]

On May 28, 9:34 pm, Serge Rielau <[e-mail]:

whitsey wrote:

Help!

I am trying to write an SQL statement (without much luck) to extract
the total number of searches and orders from a particular customer,
grouped into days/months/weeks (depending on the report type.

The tables are as follows:

Searches
--------------
search_id (PKEY)
customer_id (FKEY)
product_id
cost
date
....

Orders
-----------
order_id (PKEY)
search_id (FKEY)
order_value
date
...

Groups
-----------
group_id (PKEY)
customer_id
...

Customers
---------------
customer_id (PKEY)
....

What I want to achieve from this is

For all customers with Group_id = '17'

Mth 1 1244(Searches) 846(Orders)
Mth 2 2345(Searches) 1834(Orders)
Mth 3 2342(Searches) 1954(Orders)
Mth 4 2425(Searches) 2134(Orders)
Mth 5 5235(Searches) 4253(Orders)

I am clearly nowhere near the level I need to be to achieve this.

Can it be achieved in a single statement?

Yes, a join combined with GROUP BY. Use MONTH(date) to coarsify to the
month.
Why don't you post your best shot and we go from there?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

This is where I've started however this is returning no rows... Also,
I am unable to extract values by months...

SELECT
COUNT(SEARCHES.SEARCH_ID),
COUNT(SEARCHES.SEARCH_ID)
FROM
SEARCHES
INNER JOIN ORDERS
ON SEARCHES.SEARCH_ID = ORDERS.SEARCH_ID
INNER JOIN GROUPS
ON GROUPS.CUSTOMER_ID = SEARCHES.CUSTOMER_ID
WHERE
(GROUPS.GROUP_ID = 17)

[shannonw]

On May 30, 10:31 am, whitsey <[e-mail]:

On May 28, 9:34 pm, Serge Rielau <[e-mail]:

whitsey wrote:

Help!

I am trying to write an SQL statement (without much luck) to extract
the total number of searches and orders from a particular customer,
grouped into days/months/weeks (depending on the report type.

The tables are as follows:

Searches
--------------
search_id (PKEY)
customer_id (FKEY)
product_id
cost
date
....

Orders
-----------
order_id (PKEY)
search_id (FKEY)
order_value
date
...

Groups
-----------
group_id (PKEY)
customer_id
...

Customers
---------------
customer_id (PKEY)
....

What I want to achieve from this is

For all customers with Group_id = '17'

Mth 1 1244(Searches) 846(Orders)
Mth 2 2345(Searches) 1834(Orders)
Mth 3 2342(Searches) 1954(Orders)
Mth 4 2425(Searches) 2134(Orders)
Mth 5 5235(Searches) 4253(Orders)

I am clearly nowhere near the level I need to be to achieve this.

Can it be achieved in a single statement?

Yes, a join combined with GROUP BY. Use MONTH(date) to coarsify to the
month.
Why don't you post your best shot and we go from there?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

This is where I've started however this is returning no rows... Also,
I am unable to extract values by months...

SELECT
COUNT(SEARCHES.SEARCH_ID),
COUNT(SEARCHES.SEARCH_ID)
FROM
SEARCHES
INNER JOIN ORDERS
ON SEARCHES.SEARCH_ID = ORDERS.SEARCH_ID
INNER JOIN GROUPS
ON GROUPS.CUSTOMER_ID = SEARCHES.CUSTOMER_ID
WHERE
(GROUPS.GROUP_ID = 17)- Hide quoted text -

- Show quoted text -

I've uploaded a screen shot of the tables and their relationship here:
http://picasaweb.google.com/shannon.whitty/Temporary

Also, the 2nd COUNT() clause should read COUNT(ORDERS.SEARCH_ID) my
mistake!

[Brian Tkatch]

On 28 May 2007 01:30:35 -0700, whitsey <ly***********@gmail.com>
wrote:

>Help!

I am trying to write an SQL statement (without much luck) to extract
the total number of searches and orders from a particular customer,
grouped into days/months/weeks (depending on the report type.
The tables are as follows:
Searches
--------------
search_id (PKEY)
customer_id (FKEY)
product_id
cost
date
....
Orders
-----------
order_id (PKEY)
search_id (FKEY)
order_value
date
...
Groups
-----------
group_id (PKEY)
customer_id
...
Customers
---------------
customer_id (PKEY)
....
What I want to achieve from this is
For all customers with Group_id = '17'
Mth 1 1244(Searches) 846(Orders)
Mth 2 2345(Searches) 1834(Orders)
Mth 3 2342(Searches) 1954(Orders)
Mth 4 2425(Searches) 2134(Orders)
Mth 5 5235(Searches) 4253(Orders)
I am clearly nowhere near the level I need to be to achieve this.
Can it be achieved in a single statement?
Thanks

I took a quick shot at it:

SELECT
MONTH(Searches.Search_Date),
COUNT(*) Searches,
(
SELECT
COUNT(*)
FROM
Orders
WHERE
Orders.Customer_Id = Searches.Customer_Id
AND MONTH(Orders.Order_Date) = MONTH(Searches.Search_Date)
) Orders
FROM
Searches
WHERE
EXISTS
(
SELECT
*
FROM
Groups
WHERE
Groups.Customer_Id = Searches.Customer_Id
AND Groups.Group_Id = '17'
)
GROUP BY
MONTH(Search_Date)

I used a sub-select for no particular reason. a join ought to work as
well.

B.

[whitsey]

On May 31, 3:06 am, Brian Tkatch <N/Awrote:

On 28 May 2007 01:30:35 -0700, whitsey <[e-mail]>
wrote:

Help!

I am trying to write an SQL statement (without much luck) to extract
the total number of searches and orders from a particular customer,
grouped into days/months/weeks (depending on the report type.

The tables are as follows:

Searches
--------------
search_id (PKEY)
customer_id (FKEY)
product_id
cost
date
....

Orders
-----------
order_id (PKEY)
search_id (FKEY)
order_value
date
...

Groups
-----------
group_id (PKEY)
customer_id
...

Customers
---------------
customer_id (PKEY)
....

What I want to achieve from this is

For all customers with Group_id = '17'

Mth 1 1244(Searches) 846(Orders)
Mth 2 2345(Searches) 1834(Orders)
Mth 3 2342(Searches) 1954(Orders)
Mth 4 2425(Searches) 2134(Orders)
Mth 5 5235(Searches) 4253(Orders)

I am clearly nowhere near the level I need to be to achieve this.

Can it be achieved in a single statement?

Thanks

I took a quick shot at it:

SELECT
MONTH(Searches.Search_Date),
COUNT(*) Searches,
(
SELECT
COUNT(*)
FROM
Orders
WHERE
Orders.Customer_Id = Searches.Customer_Id
AND MONTH(Orders.Order_Date) = MONTH(Searches.Search_Date)
) Orders
FROM
Searches
WHERE
EXISTS
(
SELECT
*
FROM
Groups
WHERE
Groups.Customer_Id = Searches.Customer_Id
AND Groups.Group_Id = '17'
)
GROUP BY
MONTH(Search_Date)

I used a sub-select for no particular reason. a join ought to work as
well.

B.- Hide quoted text -

- Show quoted text -

Got it and it works perfectly, Thanks! ( I used a join myself )
However, the problem I have is that it is calculating the month of
january as a total of every january ever entered in the database!!! I
have tried to narrow it fown by outputting month & year but cannot get
it to work.

I changed MONTH(Searches.Search_Date) to
MONTH(Searches.Search_Date)'/'YEAR(Searches.Search_Date) but it didn't
like that.
I tried CAST(Searches.Search_Date AS DATE FORMAT 'mm/yy') however it
failed at "FORMAT"

Also, the months are in the order Jan - Dec. I am using this for a
graph and want the last month to be this month (May-07) and them going
back historically for a predefined period.

I can't seem to find much help on DATE functions on the web - maybe
I'm not searching right!

Thanks

[Serge Rielau]

http://publib.boulder.ibm.com/infoce...c/r0000814.htm
INTEGER(Searched.Search_date) / 100

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Brian Tkatch]

On 31 May 2007 00:19:46 -0700, whitsey <ly***********@gmail.com>
wrote:
<SNIP>

>
Got it and it works perfectly, Thanks! ( I used a join myself )

You're most welcome. Glad i could be of service.

>However, the problem I have is that it is calculating the month of
january as a total of every january ever entered in the database!!! I
have tried to narrow it fown by outputting month & year but cannot get
it to work.

I changed MONTH(Searches.Search_Date) to
MONTH(Searches.Search_Date)'/'YEAR(Searches.Search_Date) but it didn't
like that.
I tried CAST(Searches.Search_Date AS DATE FORMAT 'mm/yy') however it
failed at "FORMAT"

The problem is that month is not divided into years. The solution is
to GROUP BY years as well.

GROUP BY
YEAR(Searches.Search_Date),
MONTH(Searches.Search_Date)

That will first section off all data by years, and then--within each
yearly group--it will further section them by month.

>Also, the months are in the order Jan - Dec. I am using this for a
graph and want the last month to be this month (May-07) and them going
back historically for a predefined period.

Can't you use:

ORDER BY
YEAR(Searches.Search_Date) DESC,
MONTH(Searches.Search_Date) DESC

>I can't seem to find much help on DATE functions on the web - maybe
I'm not searching right!

Optimally, you should be reviewing all FUNCTIONs availble in the DB2
documentation. It's a good list to be familiar with.

B.