I am making some sort of basic error here

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I am making some sort of basic error here

Author

Topic

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-08 : 18:39:52

Basically there is a list of this 3 tables.

EFC is summary sales data by day. So basically its a summary of an item on day 1, then day 2. So all these are max values to get the most recent value. It also carries running totals.

CTB is a transaction log of individual transactions. This is necessary because the EFC data goes back in some cases years behind the transaction data. So this is the most recent period.

SellerID lookup is a lookup table.

All of these items are related by the ItemID. So I've joined all 3, but somehow probably wrong.

So I'm trying to get a SUM for the CTB.QTY. When I do that without joining EFC data, I get 5 transactions, each with QTY 1 for each. So I should get 5 transactions, qty 5 out of the result. However I'm getting 25 each time.

What am I doing wrong?


SELECT DISTINCT 
                      SellerID_Lookup.SellerName, CTB.ItemID, MAX(EFC.QTY_Sold) AS 'Total Sold', MAX(EFC.GMV) AS 'GMV', MAX(EFC.DurationDays) AS 'Days', MIN(CTB.ItemRev) 
                      AS 'ItemRev', SUM(CTB.QTY) AS 'QTY', COUNT(CTB.TransactionID) AS 'Transactions'
FROM         CTB INNER JOIN
                      EFC ON CTB.ItemID = EFC.ItemID INNER JOIN
                      SellerID_Lookup ON EFC.ItemID = SellerID_Lookup.ItemId AND CTB.ItemID = SellerID_Lookup.ItemId
WHERE     (CTB.ItemID = 290682546065)
GROUP BY CTB.ItemID, SellerID_Lookup.SellerName, CTB.TransactionID, SellerID_Lookup.ItemId

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-07-08 : 20:45:30

This means that your EFC table has more than one record for each CTB ID.
If you Group BY CTB.TransactionID, you will get one record for each transaction. Remove CTB.TransactionID from your GROUP BY clause and see if that gives you your desired results.

Jim

Everyday I learn something that somebody else already knew

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-08 : 22:12:51

Didn't seem to work. Separately these two are perfect, I'm just trying to combine the results of these two queries.

select MIN(CTB.ItemRev) AS 'ItemRev', SUM(CTB.QTY) AS 'QTY', COUNT(CTB.TransactionID) AS 'Transactions' from CTB
where itemID = 290682546065
group by CTB.ItemId

select EFC.title, EFC.ItemID, MAX(EFC.QTY_Sold) AS 'Total Sold', MAX(EFC.GMV) AS 'GMV', MAX(EFC.DurationDays) AS 'Days' from EFC
where itemID = 290682546065
group by EFC.Title, EFC.ItemID

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-08 : 22:21:35

So you can see the relevant data sets these are coming from.

select ItemId, TransactionID, ItemRev, QTY from CTB
where itemID = 290682546065

produces
290682546065 684573919019 10.00 1
290682546065 688711896019 10.00 1
290682546065 684148108019 10.00 1
290682546065 687838836019 10.00 1
290682546065 687997163019 10.00 1

select ItemId, Title, DurationDays, SellerId, GMV, QTY_Sold from efc
where itemID = 290682546065

produces

ItemId Title DurationDays SellerId GMV QTY_Sold
290682546065 DURAMAX 6.6L FUEL FILTER WATER SENSOR WRENCH LLY LB7 LBZ LMM 27 992999479 60.00 6
290682546065 DURAMAX 6.6L FUEL FILTER WATER SENSOR WRENCH LLY LB7 LBZ LMM 30 992999479 30.00 3
290682546065 DURAMAX 6.6L FUEL FILTER WATER SENSOR WRENCH LLY LB7 LBZ LMM 30 992999479 10.00 1
290682546065 DURAMAX 6.6L FUEL FILTER WATER SENSOR WRENCH LLY LB7 LBZ LMM 30 992999479 20.00 2
290682546065 DURAMAX 6.6L FUEL FILTER WATER SENSOR WRENCH LLY LB7 LBZ LMM 30 992999479 50.00 5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 00:39:04

[code]
SELECT t1.ItemId,TranCnt,TotalQty, ItemRev,DurationDays,Title, SellerId,GMV,TotalSold
FROM
(
select ItemId, COUNT(TransactionID) AS TranCnt, SUM(QTY) AS TotalQty, ItemRev
from CTB
group by ItemId, ItemRev
)t1
INNER JOIN
(
select ItemId, Title, MAX(DurationDays) AS DurationDays, SellerId, SUM(GMV) AS GMV, SUM(QTY_Sold) AS TotalSold
from efc
group by ItemId,Title, SellerId
)t2
ON t2.ItemId= t1.ItemId
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-09 : 02:33:46

That did it. I had to add the seller ID lookup.

Here's a question. So obviously I was doing this WAY WAY WAY wrong. Because my original wrong queries were thousands of percent longer.

Is it because I wasn't using local tables?

Also a logical question. So what I guess I'm understanding here, is this method creates a table out of 3 separate tables. I'm guessing the method I was using before was creating 3 tables, then trying to join them?

I'm just trying to figure out why my original queries now were SO inefficient.

Also I want to say thank you thank you thank you. Its got to be quiet a bit less enjoyable than say posting on reddit to come here and answer basic programming questions about SQL.

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-09 : 02:49:53

Do you have an email? I'm looking for a paid consultant if you do such a thing. I'm just flabbergasted at the improvement in the query time. I tried googling for your email and found a resume posting but I don't know if its still valid.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 09:51:17

quote:
Originally posted by pebkacbigdatauser

That did it. I had to add the seller ID lookup.

Here's a question. So obviously I was doing this WAY WAY WAY wrong. Because my original wrong queries were thousands of percent longer.

Is it because I wasn't using local tables?

Also a logical question. So what I guess I'm understanding here, is this method creates a table out of 3 separate tables. I'm guessing the method I was using before was creating 3 tables, then trying to join them?

I'm just trying to figure out why my original queries now were SO inefficient.

Also I want to say thank you thank you thank you. Its got to be quiet a bit less enjoyable than say posting on reddit to come here and answer basic programming questions about SQL.

The reason was because you were not pregrouping and bringing data for join. As its a many to many relationship just joining will cause it to skew totals while aggregating
welcome and glad that i could help you out on this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-09 : 14:49:22

Offer still stands if you're available for paid consulting ;)

So I'm trying to add 3 tables to this, but I'm still have trouble understanding this. I guess I really thought my life would be easy to be able to use the query designer, but that is not the case at all! So I'm still trying to understand the code. I added a 3rd table, but I need to add 3 more.

Here's what I have so far.

quote:
SELECT t1.ItemId,TranCnt,TotalQty, ItemRev,DurationDays,Title, SellerId,GMV,TotalSold, SellerName
FROM
(
select ItemId, COUNT(TransactionID) AS TranCnt, SUM(QTY) AS TotalQty, MIN(ItemRev) as ItemRev
from CTB
group by ItemId
)t1
Inner Join
(
select ItemId, Title, MAX(DurationDays) AS DurationDays, SellerId, MAX(GMV) AS GMV, MAX(QTY_Sold) AS TotalSold
from efc
group by SellerId,ItemId, Title
)t2
Cross Join
(
select SellerName, ItemId
from SellerID_Lookup
group by ItemId, SellerName
)t3

ON t2.ItemId= t1.ItemId and t2.ItemId = t3.ItemId
Where title like '%sensor%'
order by TotalSold desc

But I need to add a shipping table (joined on ItemId) and an attribute table (joined on Item Id). Also I need to add a part number table, which is not perfectly joined. I plan on joining it like this.

PNUM ON ATR.Value like '%' + PNUM.Product + '%'

The shipping table is SHIP and attribute table is ATR

Any help is appreciated. Also, a link to this exact style of programming and joins would be helpful. From what I understand, all the ones I'm joining on ItemID are just inner joins. Yet if I don't cross join the last one, it doesn't like the "on" statement.

Thanks in advance, and again the offer stands. Free is good, but paid is fair at a point, and I have plenty of things to still figure out and get working on this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 16:14:13

why are you cross joining third table? it will cause it to return a cartesian product.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-09 : 16:15:35

quote:
Originally posted by visakh16

why are you cross joining third table? it will cause it to return a cartesian product.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Because if I don't it through s up its hands at the "on" clause. To be honest I don't really know what I'm doing. The query designer was helping me through this and it mislead me!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 16:21:45

can you explain what exactly you're trying to do with third table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-09 : 16:35:48

If you can't see an image above this, the URL is below.
[url]http://i.imgur.com/Lx0IW.png[/url]

SHIP contains shipping price information. ATR contains attribute information (like a UPC).

PNUM has product information (like a UPC). But they are not exact matches.

So I want to join SHIP & ATR by ItemID (like the other tables. That way I can select the shipping cost. And then I want to join PNUM and ATR with

PNUM ON ATR.Value like '%' + PNUM.Product + '%'

Since they are not a perfect match (ex A123 in product catalog might be A123 B123 in attributes)

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-10 : 11:32:12

Bump ;)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-10 : 12:29:24

sounds like this


SELECT required columns...
FROM PNUM p
JOIN ATR a
ON a.Value LIKE '%' + p.Product + '%'
JOIN SHIP s
ON s.ItemID = a.ItemID
WHERE any other conditions...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources