Re: Advanced SQL - Extracting values by months

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)

.

Relevant Pages