query assistance: gurus

Hi,

I'm trying to put together a query which will combine a number of values from several database tables. Currently, there are two tables in question, one of products, and one of order details.

tblProducts:
ProductID
Product_Category
Product_Weight

tblDetails:
DetailID
Order_ID (linked to an order profile table)
Product_ID (linked to tblProducts.ProductID)
Product_Category
Detail_Quantity

What I need to achieve is a query (or several queries) that will retrieve the total weight of a detail record (ie tblProducts.ProductWeight * tblDetails.Detail_Category) and then group this by Product_Category. What you end up with is the total weight of each category of product - this will be used to calculate freight costs.

I can construct a query to retrieve total quantities by category, and total weight per detail row, but how can I combine these?

Retrieve total weight per detail row:

SELECT tblProducts.ProductID, tblProducts.Product_Weight, tblDetails.Detail_Quantity, (tblDetails.Detail_Quantity*tblProducts.Product_We ight) AS FreightWeight, tblDetails.DetailID, tblDetails.Order_ID
FROM tblProducts INNER JOIN tblDetails ON tblProducts.ProductID = tblDetails.Product_ID
WHERE (((tblDetails.Order_ID)= *xxx* ));

Retrieve total number of products ordered by category

SELECT tblDetails.Product_Category, Sum(tblDetails.Detail_Quantity) AS TotalQuantity
FROM tblDetails
GROUP BY tblDetails.Product_Category;

Any suggestions would be great!

BenHow about:

SELECT tblDetails.Product_Category, Sum(tblDetails.Detail_Quantity*tblProducts.Product _Weight) AS TotalWeight
FROM tblDetails
INNER JOIN tblDetails ON tblProducts.ProductID = tblDetails.Product_ID
GROUP BY tblDetails.Product_Category;|||Thanks Andrew,

When I try to run this query, I get a Syntax error in the JOIN statement. I've tried recreating this (unfortunately in Access) and I get an error which I can't understand:

'You tried to execute a query that does not include the specified expression 'Product_ID' as part of an aggregate function'

This error is repeated for any fields included, other than those in the SUM function. Is this just due to wiggy JetSQL? Your query structure makes sense to me, but I can't make it work.

Any other ideas?

Thanks again,

Ben|||No, that doesn't make sense to me. I presume you fixed the error in my code?

SELECT tblDetails.Product_Category, Sum(tblDetails.Detail_Quantity*tblProducts.Product _Weight) AS TotalWeight
FROM tblDetails
INNER JOIN tblProducts ON tblProducts.ProductID = tblDetails.Product_ID
GROUP BY tblDetails.Product_Category;|||Andrew - you're a legend!

I thought I had fixed that error, but I succeeded only in compounding the problem. This query of yours works a treat. Thanks very much.

All the best,

Ben

I have a question for the T-SQL gurus out there.
I need to find all Names where there are 10 consecutive "Yes"
Values. The table has upwards of 30 million records.
Does anyone have an idea how to structure this query?
CREATE TABLE (
Name varchar(50)
,Value char(1)
,dDate datetime)
Sample Data
Name Value dDate
David, Yes, 1/1/2004
David, Yes, 1/2/2004
David, No, 1/3/2004
David, Yes, 1/4/2004
David, Yes, 1/5/2004
David, Yes, 1/6/2004
Chris, Yes, 1/6/2004
David, Yes, 1/7/2004Can you be more precise? By consecutive, do you mean "on consecutive days",
or "on any days, but uninterrupted in time by a NO"?
Perhaps you can post a few more of your 30,000,000 rows, so we have
sample data for which there are some results? If possible, post
sample data as INSERT .. VALUES statements that match the
CREATE TABLE statement. Then we can paste it into Query
Analyzer and use it without correcting your errors. 'Yes' and 'No' do
not fit into a char(1) column, for example.
Finally, are the dates constrained to be time-less, and is (Name,dDate)
a key (or guaranteed to be unique)? And does 1/4/2004 mean January
4, 2004, or does it mean April 1, 2004?
Steve Kass
Drew University
Dave wrote:

>I have a question for the T-SQL gurus out there.
>I need to find all Names where there are 10 consecutive "Yes"
>Values. The table has upwards of 30 million records.
>Does anyone have an idea how to structure this query?
>
>CREATE TABLE (
> Name varchar(50)
> ,Value char(1)
> ,dDate datetime)
>Sample Data
>Name Value dDate
>David, Yes, 1/1/2004
>David, Yes, 1/2/2004
>David, No, 1/3/2004
>David, Yes, 1/4/2004
>David, Yes, 1/5/2004
>David, Yes, 1/6/2004
>Chris, Yes, 1/6/2004
>David, Yes, 1/7/2004
>
>|||Sorry for being unclear.
I mean 10 (lets just say 3 for simplicity) Yes values in a row based of
dDate (datetime). cName,nValue ,dDate form the primary key... for this
example) should be unique.
I hope that answers your questions, if not please restate and I will do
my best to be clear.
Thanks for your interest!
As you can see from
select *
from myTest
order by cName,dDate
David,Peter have 3 consecutive Yes values while Chris and Chad do not.
CREATE TABLE myTest(
cName varchar(50)
,nValue char(3)
,dDate datetime )
INSERT INTO myTest(cName,nValue ,dDate)
SELECT 'David','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 17:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 18:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-08 16:26:31.077'
UNION
SELECT 'Chris','No','2005-08-08 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-09 16:26:31.077'
UNION
SELECT 'David','No','2005-08-10 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-11 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-13 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-14 16:26:31.077'|||Sorry for being unclear.
I mean 10 (lets just say 3 for simplicity) Yes values in a row based of
dDate (datetime). cName,nValue ,dDate form the primary key... for this
example) should be unique.
I hope that answers your questions, if not please restate and I will do
my best to be clear.
Thanks for your interest!
As you can see from
select *
from myTest
order by cName,dDate
David,Peter have 3 consecutive Yes values while Chris and Chad do not.
CREATE TABLE myTest(
cName varchar(50)
,nValue char(3)
,dDate datetime )
INSERT INTO myTest(cName,nValue ,dDate)
SELECT 'David','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 17:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 18:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-08 16:26:31.077'
UNION
SELECT 'Chris','No','2005-08-08 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-09 16:26:31.077'
UNION
SELECT 'David','No','2005-08-10 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-11 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-13 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-14 16:26:31.077'|||Sorry for being unclear.
I mean 10 (lets just say 3 for simplicity) Yes values in a row based of
dDate (datetime). cName,nValue ,dDate form the primary key... for this
example) should be unique.
I hope that answers your questions, if not please restate and I will do
my best to be clear.
Thanks for your interest!
As you can see from
select *
from myTest
order by cName,dDate
David,Peter have 3 consecutive Yes values while Chris and Chad do not.
CREATE TABLE myTest(
cName varchar(50)
,nValue char(3)
,dDate datetime )
INSERT INTO myTest(cName,nValue ,dDate)
SELECT 'David','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 17:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 18:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-08 16:26:31.077'
UNION
SELECT 'Chris','No','2005-08-08 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-09 16:26:31.077'
UNION
SELECT 'David','No','2005-08-10 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-11 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-13 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-14 16:26:31.077'|||Sorry for being unclear.
I mean 10 (lets just say 3 for simplicity) Yes values in a row based of
dDate (datetime). cName,nValue ,dDate form the primary key... for this
example) should be unique.
I hope that answers your questions, if not please restate and I will do
my best to be clear.
Thanks for your interest!
As you can see from
select *
from myTest
order by cName,dDate
David,Peter have 3 consecutive Yes values while Chris and Chad do not.
CREATE TABLE myTest(
cName varchar(50)
,nValue char(3)
,dDate datetime )
INSERT INTO myTest(cName,nValue ,dDate)
SELECT 'David','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 17:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 18:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-08 16:26:31.077'
UNION
SELECT 'Chris','No','2005-08-08 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-09 16:26:31.077'
UNION
SELECT 'David','No','2005-08-10 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-11 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-13 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-14 16:26:31.077'|||CREATE TABLE answers(
aName varchar(50)
,Value char(1)
,dDate datetime)
go
-- select only 3 consecutive Ys to keep it short
insert into answers values('Joe','Y','9/17/2001')
insert into answers values('Joe','Y','9/18/2001')
insert into answers values('Joe','N','9/19/2001')
insert into answers values('Joe','Y','9/20/2001')
insert into answers values('Joe','Y','9/21/2001')
insert into answers values('Jill','N','9/19/2001')
insert into answers values('Jill','Y','9/20/2001')
insert into answers values('Jill','Y','9/21/2001')
insert into answers values('Jill','Y','9/22/2001')
insert into answers values('Jack','Y','9/21/2001')
insert into answers values('Jack','Y','9/22/2001')
go
CREATE TABLE #answers( id int identity
,aName varchar(50)
,Value char(1)
,dDate datetime)
go
insert into #answers(aname,value,ddate)
select aname,value,ddate from answers
order by aname, ddate
go
select distinct aname from #answers a
where exists(select aname from #answers b
-- 3 consecutive Ys to keep it short
where a.id=b.id+2 and a.aname=b.aname)
and not exists(select aname from #answers b
-- 3 consecutive Ys to keep it short
where b.id between a.id-2 and a.id and a.aname=b.aname and b.value='N')
drop table answers
drop table #answers
it works, but it would be interesting to figure out if it performs well
enough in your invironment. Instead of using identity, you could use
row_number() if you have it|||You need keys and constraints on the data. Let me do this in Standard
SQL and leave it to you to translate into dialect. Is this what you
meant?
CREATE TABLE Quiz
(contestant_name VARCHAR(30) NOT NULL,
answer_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
answer_flag CHAR(1) DEFAULT 'Y' NOT NULL
CHECK (answer_flag IN ('Y', 'N')),
PRIMARY KEY (foo_name, answer_date));
SELECT DISTINCT Q1.person_name
FROM Quiz AS Q1
AND 'Y'
= ALL (SELECT Q2.answer
FROM Quiz AS Q2
WHERE Q2.answer_date
BETWEEN Q1.answer_date AND Q1.answer_date +
INTERVAL 9 DAY);|||>> SELECT DISTINCT Q1.person_name
FROM Quiz AS Q1
AND 'Y'
= ALL (SELECT Q2.answer
FROM Quiz AS Q2
WHERE Q2.answer_date
BETWEEN Q1.answer_date AND Q1.answer_date +
INTERVAL 9 DAY);
<<
this is a wrong answer:
1. the subquery does not take into account contestant_name. So id Joe
always answers Yes from 1/1 to 1/11, but Jill gives a No at 1/5, Joe
will not be selected.
2. INTERVAL 9 DAY clause implies that each contestant answers exactly 1
question a day, which is not the case, just look at the OP's sample data|||Here is one way to do it:
select
distinct T.cName
from (
select
T1.cName,
T1.dDate as YesDate,
coalesce(min(T2.dDate),'99991231') as NextNoDate
from myTest as T1
left outer join myTest as T2
on T2.cName = T1.cName
and T2.nValue = 'No'
and T2.dDate > T1.dDate
where T1.nValue = 'Yes'
group by T1.cName, T1.dDate
) as R
join myTest as T
on T.cName = R.cName
and T.dDate >= R.YesDate
and T.dDate < R.NextNoDate
group by T.cName, R.YesDate
having count(*) >= 3
I am assuming the key is (cName, dDate), without nValue,
but if I'm wrong, you will have to tweak this to make it do
what you want when there are Yes and No values at the
same time for the same person.
SK
Dave wrote:

>Sorry for being unclear.
>I mean 10 (lets just say 3 for simplicity) Yes values in a row based of
>dDate (datetime). cName,nValue ,dDate form the primary key... for this
>example) should be unique.
>I hope that answers your questions, if not please restate and I will do
>my best to be clear.
>Thanks for your interest!
>As you can see from
>select *
>from myTest
>order by cName,dDate
>David,Peter have 3 consecutive Yes values while Chris and Chad do not.
>
>
>CREATE TABLE myTest(
> cName varchar(50)
> ,nValue char(3)
> ,dDate datetime )
>INSERT INTO myTest(cName,nValue ,dDate)
>SELECT 'David','Yes','2005-08-02 16:26:31.077'
> UNION
>SELECT 'Peter','Yes','2005-08-02 16:26:31.077'
> UNION
>SELECT 'Chris','Yes','2005-08-02 16:26:31.077'
> UNION
>SELECT 'Chad','Yes','2005-08-03 16:26:31.077'
> UNION
>SELECT 'Peter','Yes','2005-08-03 16:26:31.077'
> UNION
>SELECT 'Peter','Yes','2005-08-03 17:26:31.077'
> UNION
>SELECT 'Peter','Yes','2005-08-05 16:26:31.077'
> UNION
>SELECT 'David','Yes','2005-08-05 16:26:31.077'
> UNION
>SELECT 'Peter','Yes','2005-08-05 18:26:31.077'
> UNION
>SELECT 'Chad','Yes','2005-08-07 16:26:31.077'
> UNION
>SELECT 'David','Yes','2005-08-07 16:26:31.077'
> UNION
>SELECT 'Chad','No','2005-08-08 16:26:31.077'
> UNION
>SELECT 'Chris','No','2005-08-08 16:26:31.077'
> UNION
>SELECT 'David','Yes','2005-08-09 16:26:31.077'
> UNION
>SELECT 'David','No','2005-08-10 16:26:31.077'
> UNION
>SELECT 'David','Yes','2005-08-11 16:26:31.077'
> UNION
>SELECT 'Chad','No','2005-08-13 16:26:31.077'
> UNION
>SELECT 'Chris','Yes','2005-08-14 16:26:31.077'
>
>

Monday, March 26, 2012

Query gurus please?

Wednesday, March 7, 2012

query consecutive valid rows?

Monday, February 20, 2012

Query Assistance Requested.. Summing Data

query assistance

Blog Archive

About Me