Re: Advanced SQL - Extracting values by months


"David Cressey" <cressey73@xxxxxxxxxxx> wrote in message
news:4pz7i.17500$zN5.6189@xxxxxxxxxxx

"whitsey" <lysterfieldcc@xxxxxxxxx> wrote in message
news:1180340909.593482.109010@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.

Let's tackle a simpler query first, one that just delivers the months and
the searches.


select
DatePart ("m", s.date) as Month,
count (s.search_id) as Searches
from
searches s inner join
groups g on s.customer_id = g.customer_id
where
g.group_id = 17
group by
DatePart ("m", s.date);



Note: DatePart is on MS SQL. You may need to use a different date
function.
The m inside double quotes is MS dialect. You may need single quotes.
I'm
assuming Group_id is a number.

If this delivers correct results, the next step is to devise a similar
query, but for orders. Later, we'll combine the two queries.

I still have criticisms of your table design, but you are clearly not
ready
to hear them.

In particular, the foreign key: ORDERS.SEARCH_ID restricts the data to
many orders connected to one search. It sounds like that's not your
intent.
But given how you reacted to the last criticism I made of the table
design,
this comment is probably wasted.



/* The following gets the search count by date: */


select
s.date,
count (s.search_id) as searches
from
searches s inner join
groups g on s.customer_id = g.customer_id
where
g.group_id = 17
group by
s.date;


/* Now for one the gets the order count by date: */

select
o.date,
count (o.order_id) as orders
from
(orders o inner join
searches s on o.search_id = s.search_id) inner join
groups g on s.customer_id = g.customer_id
where
g.group_id = 17
group by
s.date;


/*once these have been checked out to make sure they work, we can paste
them together to get the result in one query. */




Now let's look


.

Relevant Pages