June 1, 2009 at 9:04 am
Please help me with the following SQL (I'm new to SQL Server), I'm trying count individual column from child tables - I want to display all of this in a datagrid - thing is I get a row for every child, but only want to show a unique parent rows with its associated child column counts in this row. - in the below tbljMultipleAuctions is the top most parent table.
SELECT
ma.MultipleID,
ai.AuctionItemID,
ma.Title,
ma.MultiType,
ma.AucDate,
sr.RegionName,
SUM(xwv.HitsAD) AS Web_View_Total,
COUNT(xo.asset_id) as Offers_Total,
COUNT(xb.webref) as Brokers_Total,
COUNT(xpr.multiple_id) as Register_Total,
COUNT(xsh.webref) as Showhouse_Total
FROM
tbljMultipleAuctions as ma
LEFT JOIN
tblxsearch_regions as sr
ON (sr.SearchRegionID = ma.SearchRegionID)
LEFT JOIN
tbl4auction_items as ai
ON (ai.MultipleAuctionID = ma.MultipleID)
LEFT JOIN
tbl7auction_report as xwv
ON (xwv.AuctionHotelID = ai.AuctionItemID)
LEFT JOIN
offers xo
ON(xo.asset_id = ai.AuctionItemID )
LEFT JOIN
brokers xb
ON(xb.webref = ai.AuctionItemID )
LEFT JOIN
pre_registration as xpr
ON(xpr.multiple_id = ma.MultipleID )
LEFT JOIN
show_house as xsh
ON(xsh.webref = ai.AuctionItemID)
GROUP BY ma.MultipleID, ai.AuctionItemID, ma.Title, ma.MultiType, ma.AucDate, sr.RegionName, xsh.webref
June 1, 2009 at 9:10 am
could post some sample data of input and output data.
Please also refer to the first link in my sig for posting , that will result in the faster help from others here 🙂
Oh and welcome to SSC
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 2, 2009 at 2:05 am
Christopher, basically all tables are joined on (Int) PKs and FKs and tbljMultipleAuctions is the Top parent table for which I want unique rows.
Please help as I need to get working pretty soon.
thanks a mil.
June 2, 2009 at 2:09 am
it all depends where your duplicates are coming from?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 2, 2009 at 4:08 am
Christopher,
I think in a nutshell what I'm asking is, why is GROUP BY not giving me unique result rows - I have tried GROUP BY, HAVING, UNION - nothing works, I keep getting the same amount of rows that I have child records for.
please help
June 2, 2009 at 4:12 am
You have xsh.webref in the group by and as a count in the select statement, try removing this from the Group By to see if this gives you the expected result.
GROUP BY ma.MultipleID, ai.AuctionItemID, ma.Title, ma.MultiType, ma.AucDate, sr.RegionName
Also it would make it easier to help if could post some sample data.
June 2, 2009 at 4:58 am
Christopher,
removing the webref had no effect - here is my output.
MultipleID Date Region Category Description Web Views Offers Made Broker Interest Auction register (m:m) Show-house
128 2009-05-05 Gauteng Commercial Residential [Pretoria] 168 0 0 2 0
128 2009-05-05 Gauteng Commercial Residential [Pretoria] 102 2 2 2 0
128 2009-05-05 Gauteng Commercial Residential [Pretoria] 148 0 0 2 0
128 2009-05-05 Gauteng Commercial Residential [Pretoria] 268 0 0 2 0
as you can see 128 is the parent table tbljMultipleAuctions PK - result output should be ONLY 1 row with accumulative amounts for the COUNT() and SUM() on columns from the children tables
June 2, 2009 at 5:00 am
what query did you use for that sample data?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 2, 2009 at 5:14 am
christof_c (6/2/2009)
Christopher,removing the webref had no effect - here is my output.
MultipleID Date Region Category Description Web Views Offers Made Broker Interest Auction register (m:m) Show-house
128 2009-05-05 Gauteng Commercial Residential [Pretoria] 168 0 0 2 0
128 2009-05-05 Gauteng Commercial Residential [Pretoria] 102 2 2 2 0
128 2009-05-05 Gauteng Commercial Residential [Pretoria] 148 0 0 2 0
128 2009-05-05 Gauteng Commercial Residential [Pretoria] 268 0 0 2 0
as you can see 128 is the parent table tbljMultipleAuctions PK - result output should be ONLY 1 row with accumulative amounts for the COUNT() and SUM() on columns from the children tables
These results contain the MultipleId but not the ai.AuctionItemID that is contained in your SELECT statment, this could be the cause of the problems, But to avoid further guess work investigation I will need to see some sample data.
June 2, 2009 at 5:16 am
Christopher - thanks for the help.
I used the same query as in the beginning of this post.
all I need is one row with accumulative child values and not duplicate rows for every parent record
128 2009-05-05 Gauteng Commercial Residential [Pretoria] 686 2 2 8
June 2, 2009 at 5:27 am
I can't see how that is possible as the field's don't match up to what you have posted as sample results...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 2, 2009 at 6:26 am
I see this conversation is going nowhere - thanks for the effort, but I'll try and solve this on my own. I 've done more complex sql than this in MySQL where GROUP BY and Aggregates are respected and operates as one would expect, without this unpredictable exprerience.
June 2, 2009 at 6:29 am
I'm pretty sure that sql you are writing is doing what it is supposed to do.
It's just a matter or asking sql correctly 🙂
but yeah I would say there is something in your group by clause that is not in your select clause that is causing duplicates.
but without seeing the EXACT query you ran to get those results it's impossible say much more
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply