Tricky Select Query - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Tricky Select Query

Author

Topic

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-12-26 : 13:25:16

I have one table that collects a count on differnt areas. Basically it updates with another 1 each time a page is loaded on the website.

What I want to do is put together a report that can basically only pull in the items that have a count in the table. For instance I have 700 items but only want to pull in the records that have items in the database, Say 50 of those 700 items.

But in the database out of those 50 you could have 7 items for one of those 50 items. I have figured out the count to total the items 7 times on just doing a report for the single item. But want a report that would show all items in the table on the same report. Plus I have other description data in another table for each of the items I want to include in the report.

Table 1 is the collection table
TableID, ItemID, ItemCount

Example:
TableID: 1, ItemID: 1, ItemCount: 1
TableID: 2, ItemID: 10, ItemCount: 1
TableID: 3, ItemID: 20, ItemCount: 1
TableID: 4, ItemID: 405, ItemCount: 1
TableID: 5, ItemID: 700, ItemCount: 1

Second Desrcitpion Table
ItemID, Item, ItemLogo

ItemID: 1, Item: Work Boots, ItemLogo: Boots.gif
ItemID: 10, Item: Knifes, ItemLogo: Knife.gif
ItemID: 20, Item: Discs, ItemLogo: Disc.gif
ItemID: 405, Item: Tools, ItemLogo: Tools.gif
ItemID: 700, Item: Bolts, ItemLogo: Bolts.gif

Resulting Report:
Work Boots, Boots.gif, ItemCount: 10
Knifes, Knife.gif, ItemCount: 12
Discs, Discs.gif, ItemCount: 15
Tools, Tools.gif, ItemCount: 52
Bolts, Bolts.gif, ItemCount: 92

I am guessing it would require a double join of some sort in the SQL statement.

Any help would be much appreciated..

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-26 : 15:58:50

Are You still having trouble?
Post the SQL that You have so far...

rockmoose

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-12-26 : 20:34:25

SELECT SUM(ItemCount) AS [ReviewCount] from tbl_AD_Stats"

This is in ASP code SQL statement that pulls the one item at a item at a time report.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-26 : 20:49:38

Use QA (Query Analyzer) to test and work with your SQL before putting it in the ASP page.
Are You after something like this?:

SELECT t2.Item, t2.ItemLogo, SUM(t1.ItemCount) AS [ItemCount]
FROM tbl_AD_Stats t1 JOIN tbl_Description t2 ON t1.ItemID = t2.ItemID
GROUP BY t2.Item, t2.ItemLogo

rockmoose

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-12-26 : 21:27:10

That seems to have worked in Query Analyzer. I will need to group by one more item but will try and figure that out on my own for now. I appreciate your help rockmoose!

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-12-30 : 12:13:46

quote:
Originally posted by rockmoose

Use QA (Query Analyzer) to test and work with your SQL before putting it in the ASP page.
Are You after something like this?:

SELECT t2.Item, t2.ItemLogo, SUM(t1.ItemCount) AS [ItemCount]
FROM tbl_AD_Stats t1 JOIN tbl_Description t2 ON t1.ItemID = t2.ItemID
GROUP BY t2.Item, t2.ItemLogo

rockmoose

This worked for the sum of one item, what if I wanted to use another field that I needed to group the SUM ItemCount down by? I have another field called AREAID that tells me where the count is coming from.

So if I use my original example with more detail the report would look like this.

Work Boots, Boots.gif, AreaID 1, ItemCount: 5
Work Boots, Boots.gif, AreaID 2, ItemCount: 3
Work Boots, Boots.gif, AreaID 3, ItemCount: 2

Knifes, Knife.gif, AreaID 1, ItemCount: 6
Knifes, Knife.gif, AreaID 2, ItemCount: 4
Knifes, Knife.gif, AreaID 3, ItemCount: 2

Discs, Discs.gif, AreaID 1, ItemCount: 5
Discs, Discs.gif, AreaID 2, ItemCount: 4
Discs, Discs.gif, AreaID 3, ItemCount: 6

Tools, Tools.gif, AreaID 1, ItemCount: 20
Tools, Tools.gif, AreaID 2, ItemCount: 19
Tools, Tools.gif, AreaID 3, ItemCount: 13

Bolts, Bolts.gif, AreaID 1, ItemCount: 50
Bolts, Bolts.gif, AreaID 1, ItemCount: 30
Bolts, Bolts.gif, AreaID 1, ItemCount: 12

I am thinking there would be a way to do this with one SQL statement. Thanks for all your help.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-02 : 15:50:59

>> This worked for the sum of one item, what if I wanted to use another field that I needed to group the SUM ItemCount down by? I have another field called AREAID that tells me where the count is coming from.
Just one item?

>> I have another field called AREAID that tells me where the count is coming from.
You would have to group by the AREAID field as well.

If You are still stuck, please post the complete table DDL, and the SQL You have so far.
I cannot tell in which table the AREAID field is located.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources