Re: Advanced SQL - Extracting values by months


"whitsey" <lysterfieldcc@xxxxxxxxx> wrote in message
news:1180485513.899821.135320@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On May 29, 8:35 pm, "David Cressey" <cresse...@xxxxxxxxxxx> wrote:
"whitsey" <lysterfiel...@xxxxxxxxx> wrote in message

news:1180410766.420129.71610@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx





On May 29, 12:23 am, "David Cressey" <cresse...@xxxxxxxxxxx> wrote:
"whitsey" <lysterfiel...@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'

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?- Hide quoted text -

- Show quoted text -

I'm not having an issue with the table structure, it is the SQL
required to extract the information I want.

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.

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

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.-
Hide quoted text -

- Show quoted text -

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)


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.








.

Relevant Pages