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

"whitsey" <lysterfieldcc (AT) gmail (DOT) com> wrote

Let's stop right here.

Customer_id should not be a column in the Groups table. The way you've
placed it, it looks like a customer can belong to many groups, but a group
can only have one customer in it. I doubt that this is your intent.

More likely would be a "group_id" column in the Customers table. Do you
agree?

On May 29, 12:23 am, "David Cressey" <cresse... (AT) verizon (DOT) net> wrote:
I'm not having an issue with the table structure, it is the SQL
required to extract the information I want.

A Group can have multiple customers in it and a customer can be a part
of multiple groups.

"whitsey" <lysterfieldcc (AT) gmail (DOT) com> wrote

Perhaps you should be having an issue with it. If your table structure is
wrong, you won't be able to store the right facts (data). If your database
doesn't have the right data, working out a correct query is going to be
awfully difficult, perhaps impossible.


In that case, you need an extra table, call it "customer_groups", with
two columns:
customer_id and group_id. The PKEY of this table is both columns.

Do you agree that this is the correct table structure for your data? If so,
then we can proceed to work out what the query is going to look like.

On May 29, 8:35 pm, "David Cressey" <cresse... (AT) verizon (DOT) net> wrote:
You guys are missing the point - The database structure works fine for
our needs - sure, it could be refined or improved but that is not what
I am seeking assistance with!!!!!! I'm tring to create an SQL
statement to extract a count of the search & prder log files.

Here is where I have started:

SELECT
COUNT(SEARCHES.SEARCH_ID),
COUNT(ORDERS.ORDER_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)

"whitsey" <lysterfieldcc (AT) gmail (DOT) com> wrote

I'm not missing the point. Perhaps when you've been working with databases
for several years, you will see the connection between the question you
started with and the issue I raised. It's not as irrelevant as you may
think.

In the meantime....

Have you tested your query using test data? In your test data, do you have
a more than one customer in Group 17?

"whitsey" <lysterfieldcc (AT) gmail (DOT) com> wrote

First, You will need to use two tools to get the query that you want. One
is GROUP BY and the other is a function that converts dates to months. The
function that converts dates to months is DBMS specific. You might want to
tell us what function you can use to convert a date to a month.


It's not clear to me from your table structure or from your sample answer
whether the date of a search is always the same as the date of the
corresponding order. If so, no problem. If not, then it's not clear how
you want the orders and dates grouped for counting purposes.

There are a few DBMSes that don't support the use of a function in the GROUP
BY clause. Hopefully, this problem won't come up in this case. There is a
way of dealing with such an obstacle, but I don't wsnt to bother posting it
if I don't have to.

"whitsey" <lysterfieldcc (AT) gmail (DOT) com> wrote


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.

"David Cressey" <cressey73 (AT) verizon (DOT) net> wrote



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

"whitsey" <lysterfieldcc (AT) gmail (DOT) com> wrote

Here's the daily report, made by joining two subqueries together:

select
scount.date,
scount.searches,
ocount.orders

from
(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) as scount full outer join
(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) as ocount on scount.date = ocount.date;


What's interesting about the above is that the outer join condition is on
the date, and not the customer_id.

Doing it by month or week is the same idea, except you use a built in
function to extract the month number or the week number from the date in a
few places. An alternative to using such a function in the query is to
build an "almanac" table with three columns: day, week, and month.
We did something like that when building a reporting database back in 1994.
We had lots more columns in the almanac table... things like what fiscal
quarter the date belonged to and whether the enterprise was or was not open
that day.

The almanac table made lots of queries a lot simpler. It's sort of like
making a "time dimension" in a multidimensional data mart.