Help with Joins

Hi All,
I'm trying to write a query that will return the number of products sold,
and the total value of each products sales.
Heres what I have so far:
SELECT COUNT(cartrows.idProduct) AS QtySold, cartrows.idProduct AS
idProduct
FROM cartrows INNER JOIN
carthead ON cartrows.idOrder = carthead.idOrder
GROUP BY cartrows.idProduct
ORDER BY cartrows.idProduct
This works Ok, but I only want to return orders where the status is either
1, 2 or 7. What I came up with is below, which returns one line per
idProduct, per OrderStatus:
SELECT TOP 100 PERCENT COUNT(cartrows.idProduct) AS QtySold,
cartrows.idProduct AS idProduct,
carthead.orderStatus
FROM cartrows INNER JOIN
carthead ON cartrows.idOrder = carthead.idOrder
GROUP BY cartrows.idProduct, carthead.orderStatus
HAVING (carthead.orderStatus IN ('1', '2', '7'))
ORDER BY cartrows.idProduct
I guess this is the result I expect from this, I'm just not sure what I need
to do so I only get one line per idProduct, with the qty sold, only from
orders with an orderStatus of 1, 2 or 7.
Any help will be much appreciated. I've include table design statements
below.
Thanks!
Simon.
CREATE TABLE [carthead] (
[idOrder] [int] IDENTITY (1, 1) NOT NULL ,
[idCust] [int] NULL ,
[orderDate] [datetime] NULL ,
[orderDateInt] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[randomKey] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[subTotal] [float] NULL ,
[taxTotal] [float] NULL ,
[shipmentTotal] [float] NULL ,
[Total] [float] NULL ,
[shipmentMethod] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[lastName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[customerCompany] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[phone] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[email] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[address] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[city] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[locState] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[locCountry] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[zip] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[shippingName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLastName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingAddress] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingCity] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLocState] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLocCountry] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingZip] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[paymentType] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardType] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardNumber] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardExpMonth] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[cardExpYear] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[cardVerify] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[cardName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[generalComments] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[orderStatus] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[auditInfo] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[storeComments] [text] COLLATE Latin1_General_CI_AS NULL ,
[storeCommentsPriv] [text] COLLATE Latin1_General_CI_AS NULL ,
[adjustAmount] [float] NULL ,
[adjustReason] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[taxExempt] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[discCode] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[discPerc] [float] NULL ,
[discTotal] [float] NULL ,
[shippingPhone] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[handlingFeeTotal] [float] NULL ,
[idAffiliate] [int] NULL ,
[commPerc] [float] NULL ,
[otherFeeTotal] [float] NULL ,
[backOrder] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[idOrder]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [cartrows] (
[idCartRow] [int] IDENTITY (1, 1) NOT NULL ,
[idOrder] [int] NULL ,
[idProduct] [int] NULL ,
[sku] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[quantity] [int] NULL ,
[unitPrice] [float] NULL ,
[unitWeight] [float] NULL ,
[description] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
[downloadCount] [int] NULL ,
[downloadDate] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[taxExempt] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[idDiscProd] [int] NULL ,
[discAmt] [float] NULL ,
PRIMARY KEY CLUSTERED
(
[idCartRow]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GODDL, great! Thanks!
Move the condition to the WHERE clause (untested, since you haven't provided
any sample data):
select count(cartrows.idProduct) as QtySold
,cartrows.idProduct as idProduct
from cartrows
inner join carthead
on cartrows.idOrder = carthead.idOrder
where (carthead.orderStatus in ('1', '2', '7'))
group by cartrows.idProduct
order by cartrows.idProduct
As I see it you want to restrict the result before the rows are grouped -
this is what WHERE does. The HAVING clause restricts results *after* the row
s
have been grouped.
ML
http://milambda.blogspot.com/|||Thank you for your reply - Especially the explanation of WHERE Vs HAVING.
Simon.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0C331587-2374-4C79-A6BD-12F590E0FBC4@.microsoft.com...
> DDL, great! Thanks!
> Move the condition to the WHERE clause (untested, since you haven't
> provided
> any sample data):
> select count(cartrows.idProduct) as QtySold
> ,cartrows.idProduct as idProduct
> from cartrows
> inner join carthead
> on cartrows.idOrder = carthead.idOrder
> where (carthead.orderStatus in ('1', '2', '7'))
> group by cartrows.idProduct
> order by cartrows.idProduct
> As I see it you want to restrict the result before the rows are grouped -
> this is what WHERE does. The HAVING clause restricts results *after* the
> rows
> have been grouped.
>
> ML
> --
> http://milambda.blogspot.com/|||NP. Just remember which NG works for you. ;)
ML
http://milambda.blogspot.com/