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]

Re: Advanced SQL - Extracting values by months

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]

Re: Advanced SQL - Extracting values by months

On May 28, 9:34 pm, Serge Rielau <srie...@ca.ibm .comwrote:

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_I D
INNER JOIN GROUPS
ON GROUPS.CUSTOMER _ID = SEARCHES.CUSTOM ER_ID
WHERE
(GROUPS.GROUP_I D = 17)

[shannonw@reynolds.com.au]

Re: Advanced SQL - Extracting values by months

On May 30, 10:31 am, whitsey <lysterfiel...@ gmail.comwrote:

On May 28, 9:34 pm, Serge Rielau <srie...@ca.ibm .comwrote:
>
>
>
>
>

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_I D
INNER JOIN GROUPS
ON GROUPS.CUSTOMER _ID = SEARCHES.CUSTOM ER_ID
WHERE
(GROUPS.GROUP_I D = 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.SE ARCH_ID) my
mistake!

[Brian Tkatch]

Re: Advanced SQL - Extracting values by months

On 28 May 2007 01:30:35 -0700, whitsey <lysterfieldcc@ 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.Custom er_Id
AND MONTH(Orders.Or der_Date) = MONTH(Searches. Search_Date)
) Orders
FROM
Searches
WHERE
EXISTS
(
SELECT
*
FROM
Groups
WHERE
Groups.Customer _Id = Searches.Custom er_Id
AND Groups.Group_Id = '17'
)
GROUP BY
MONTH(Search_Da te)

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

B.

[whitsey]

Re: Advanced SQL - Extracting values by months

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

On 28 May 2007 01:30:35 -0700, whitsey <lysterfiel...@ 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.Custom er_Id
AND MONTH(Orders.Or der_Date) = MONTH(Searches. Search_Date)
) Orders
FROM
Searches
WHERE
EXISTS
(
SELECT
*
FROM
Groups
WHERE
Groups.Customer _Id = Searches.Custom er_Id
AND Groups.Group_Id = '17'
)
GROUP BY
MONTH(Search_Da te)
>
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.S earch_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]

Re: Advanced SQL - Extracting values by months

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0000814.htm

INTEGER(Searche d.Search_date) / 100

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Brian Tkatch]

Re: Advanced SQL - Extracting values by months

On 31 May 2007 00:19:46 -0700, whitsey <lysterfieldcc@ 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.S earch_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.S earch_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.S earch_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.