Counting Only Countries That Can Sell A Product Category?
May 20, 2008
In my data, I'm looking to find how many countries purchased a specific category, and how many countries total can sell the category in question.
(only certain countries sell each category)
This will probably be represented with a many to many relationship between country and category (in order to show the restriction between category and country).
How would one write a calculation to determine how many countries purchased a specific category, and how many countries total can sell the category in question?
I have two tables, one is a list of categories, the other a list of items listed in the categories. The category table is self-referencing through a ParentID column. Top-level categories have ParentID = 0. Categories========ID intParentID intCatName varchar(30)Items====IDCategoryIDItemID There is a third table that links to items through ItemID, but this is not important for this problem! What I want to do is create a stored SQL procedure. This procedure simply pulls rows from the category table and counts the number of items that are in it. This is straighforward enough using COUNT as a "virtual column", but the difficulty is counting the items in the category but also the items in any subcategories. An end result with two top-level categories and three subcategories in each might look like: - Category 1 (20)---- Subcategory 1.1 (10)---- Subcategory 1.2 (5)---- Subcategory 1.3 (5)- Category 2 (14)---- Subcategory 2.1 (3)---- Subcategory 2.2 (4)---- Subcategory 2.3 (7)Hence the difficult bit is getting the total number in brackets for each category with subcategories. What I have at the moment is: SELECT Categories.ID, Categories.CategoryName, Categories.ParentID, (SELECT COUNT(*) FROM Items WHERE Items.CategoryID = Categories.ID) AS ItemTotalFROM Categories What I would like is something along the lines of (pseudocode): SELECT Categories.ID, Categories.CategoryName, Categories.ParentID, (SELECT COUNT(*) FROM Items WHERE <Item is in category or subcategory>) AS ItemTotalFROM Categories I don't particularly want to use temporary tables and I definitely do not want to do any of this retrieval in my application - it needs to come straight from the database. Thanks!
I can link a product to a category. Now, if I just link a product to a single category, such as the bottom leaf category:
Self Help / Personal Development / Spiritual / Meditation
I would link a product to the Meditation category. However if I click on Self Help while browsing, I want to see all items underneath Personal Development, Spiritual and Meditiation. So my question is is this a good way to store the product-category relationships, or should I put many entries into CategoryProducts to keep the queries simlpe and faster? Are they faster doing it this way? In this way there would be 4 entries for a product in meditation. My personal idea is that adding all entries up a tree arm of a category path will be cumbersome to manage, but it does solve the problem of clicking on Self Help and seeing all products that exist within sub-categories. I am sure an SQL query would be able to work this out, but I dont know if performance would be something to consider on an ecommerce site? Are there any patterns fo rthis stuff - seems a reasonably repeatable pattern for business sites?
I need to have tables of world countries and cities to use in a user registration form, I searched the Internet for scripts,xml,excel files for countries & cities lists but I couldn't find. Can anybody tell me where to get them from?
I am using the code below to get all the children of a particular product and it is working fine. How to get the particular product's id in the select statement. for example, i need to show 891 in a separate column for all the records returned by the query below.
DECLARE @Hierarchy TABLE (Product_Id INT, Parent_Product_Id INT) INSERT INTO @Hierarchy VALUES (123, 234) INSERT INTO @Hierarchy VALUES (234, 456) INSERT INTO @Hierarchy VALUES (456, 678) INSERT INTO @Hierarchy VALUES (678, 891) INSERT INTO @Hierarchy VALUES (891, NULL)
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
HelloI am using sql server 2005.I have two tables as described below.Table1UserID UserSales---------------------1 102 133 174 195 216 107 128 119 3110 2311 2412 1013 16Table2UserID Country----------------------1 Canada2 Canada3 Canada4 Canada5 Canada6 USA7 USA8 USA9 USA10 USA11 UK12 UK13 UKI want to get top 2 UserSales for each country and remaining should bedisplayed as Total as Others for that country.Can someone please help me with this query?RegardsAmit
Let's say you a 1000 records in the Employees table, who are spread over 40 different cities. How would you get a breakdown of how many employees in each city ?
Do I have to loop with a Count(*) for each CityID, or something ?
Should this be done or simply provide a category field in the hardware table? I like the extra table because it kind of ensures that a user doens't add a plate with a category value "Plate", another user adds "plate", and another user adds "Plates", etc....
I'm trying to remove a read only/stand by database that I believe was a messed-up attempt at log shipping. When I go through the interface it says 'cannot remove database because it is set up for replication'. If I try and 'directly update' the sys tables (yea I know you can't do that anymore) I get the error:
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
So my dilema is I just want to delete a database that was attempting to do log shippinig and is now stuck in read only/standby. How can this be done in SQL 2005?
I am building from the Time Tracker Start Kit, trying to get a better feel for how MS thinks we should do things.
In my editing, I have built a new Stored Procedure, trying to pull the newest entries for a specified person.
I have the following that will return all entries for a specific person, sorted by date, newest first:
SELECT EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID, TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName, ManagerUserID, TT_Categories.Abbreviation AS CatShortName FROM TT_EntryLog INNER JOIN TT_Categories ON TT_EntryLog.CategoryID = TT_Categories.CategoryID INNER JOIN TT_Projects ON TT_EntryLog.ProjectID = TT_Projects.ProjectID WHERE UserID = @UserID ORDER BY EntryDate Desc
This will return something like:
EntryLogId Description Duration EntryDate ProjectID CategoryID CategoryName ProjectName ManagerUserID CatShortName 14Can type date in date... .00 2004-02-04 10:28:00116Pros ITS Project Management App 3 Pros 12Changed "Entry Date"... .00 2004-02-03 13:28:00116Pros ITS Project Management App 3 Pros 13Added default button ... .00 2004-02-03 00:00:00116Pros ITS Project Management App 3 Pros 11Removed hours per p... .00 2004-02-03 00:00:00116Pros ITS Project Management App 3 Pros 6Isn't this cool .00 2004-02-02 00:00:00229Pros Knowledge Base 3 Pros 9Added week-by-week... .00 2004-02-02 00:00:00116Pros ITS Project Management App 3 Pros 10Fixed Update comma... .00 2004-02-02 00:00:00116Pros ITS Project Management App 3 Pros 1Built initial framewor... 6.00 2004-01-30 00:00:00116Pros ITS Project Management App 3 Pros 5Adding up to 8 hours... 2.00 2004-01-30 00:00:00229Pros Knowledge Base 3 Pros 3Debugged - fixed a f... 1.00 2004-01-23 00:00:00229Pros Knowledge Base 3 Pros
What I would like to accomplish is to return only the newest entry for each ProjectID (so in the above example, there would only be 2 entries, EntryID 14 and 6)
I'm not sure if this is a completely dumb question, but please humor me:) I have a table of records, called Records, each of which has aCategory_ID that places it in a specific category; the details of thecategories are stored in another table called Category. What I need todo is retrieve a recordset that contains one record from each category,but where the records that are retrieved are random. I know how toretrieve one or more random records using "order by NewID()", but havenot been able to work out how to get one random record from eachcategory.Any assistance in this puzzler will be HUGELY appreciated!ThanksFEB
What I would like is to write a stored procedure to browse the restaurant by either regionname or cuisinename. I tried to create the view from those three table and create the stored procedure to search from the view based on criterias I tried to search for regionName = RN_1 & CuisineName = 2 the result is empty. It is true because there is no restaurant under that region however what I want to have is list all restaurants under that RegionName children e.g RN_1 has RN_1_1 & RN_1_2 & RN_1_3 so the result should be displayed as
Is there a reference to describe the relevance of the Category column in the sysobjects table? I'm trying to track down stored procedures that are set to automatically run at startup and the only thing I can find that is consistent about those that are set to autorun is that they have a value of 16 in the Category column. Is this consistent? What else may I glean from this column?
I new to sql procedures and I need your advice on below subject. Thanks in advance.
I have products table that has catid (string 50) and subcatid (string 50) I like to transfer the string to another table (categories) and keep only int values in products catid and subcatid.
therefore I created categories table and added 3 fields;
catid int (primary and autonumber) catname string 50 will be equal to category name in products table parentid int (if it is a parent category, it will be equal to 0 otherwise if it is a subcategory it will be equal to parentcatid)
so instead writing a vb program I like to ask your advice on better solutions.
please help
Also I am using sql express 2005 and I wanted to practice (learn) sql DTS. is there any way I can download this package and work it with my system?
select [Parent Name],[ID],[Year],[Sales Name], sum([Total VtM]) as 'Total Sales' from RegData group by [Parent Name],[ID],[Year],[Sales Name] order by [Total Sales] desc
I need to modify this query to get the top 5 of each category based on Total Sales amount.
How can I store more than one category in a products table? For exampe: I have a dvd website where the admin can add new dvd's. On the website all the categories are listed with a checkbox. If the dvd is a action comedy the admin have to check these two checkboxes. But how do I store that in the sql database an retrieve it? Thanks David
Hello. I have a simple project using 3 tables: Categories, Subcategories, and Items. I have just gotten my insert, edit, and delete functions to work, but I've noticed a problem I hope someone can help with: When I delete a certain category (lets say "Restaurants"), the subcategories and items that were in that category still remain in the database/table. (If I delete "Restaurants", the subcategories "Italian", "Seafood", etc - as well as any items in those subcategories - are not deleted). So what would I need to do to delete any Subcategory that is in the deleted Category (shares a CategoryID) and then delete all Items in those Subcategories? For reference, all Subcategories in a Category have reference to that CategoryID, and all Items in a Subcategory have a "SubcategoryID" field. I understand that I need to traverse the tables and remove all Subcategories with the CategoryID being deleted, but since the Items do not have a reference to the CategoryID, how would I delete those as well?
Hi, I have a requirement where I need to display the items category wise I have 3 tables, one stores category_id, parent_id and category_name second table stored item_id,item_name and proce 3rd table stores category_id and item_id I need to display the values like this Category name, Item name with first parent category ans ites items should be displayed and next sub category and its item By category table with contain 2 levels of sub category i.e Category1 Category11 Category111 any help with the query would be much appreciated, I wrote the query something like this.... select c.category_name,parent_id,item_name from category c,items i,Category_item ci where ci.item_id=i.item_id and c.category_id=ci.category_id order by parent_id, category_nameBut it will display all parent category items then the sub category items I want to display like thiscategory1 items1category11 item11category1 item12category11 item111category11 item112 category2 item2 Thanks
Greetings,I have one table, named Article, and one table name Category.The problem is, one Article could be in just one or in several categories.What is the best way to connect data between Article and Category according to fast search performance?I have several ideas:1. To have third cross table Article_Category with fields Article_ID and Category_ID, and search Article_Category table 2. To have several INTEGER columns in Article table (like Category_ID1, Category_ID2,..) and search those columns3. Add one VARCHAR field in Article table where I could write Category ID's delimited by some character (e.g. by comma), and do text search in only that column.What is recommended for solving problems like this?
I need to sum the attendance hours by category and then group by 'Week of'. The 'Week of' start date is defined by the Monday in that week but Sunday is works too. If the Category Values are in ’Art’ or ‘PE’, they need to be combined into Non Educational. I also need to be able to flag the day(s) a student reaches 120 hours.
My table which is structured like this:
CREATE TABLE Attendance ( DOP_ID int, Category varchar(20), Title varchar(20), Date datetime, Hours int, )
[Code] ....
I need an end result which looks like this:
ID Category Week of Total Hours 4504498 GED Program 7/1/2012 26 4504498 GED Program 7/8/2012 23 4504498 High School 7/1/2012 19 4504498 High School 7/8/2012 28 9201052 Non Educational 7/15/2012 30
ID Day_120_Hours_Reached 356485 6/30/2012 356485 11/15/2012 555666 10/12/2012 555666 2/25/2013
I have been looking for examples of a Week function that will pull out the 'week of' from a date using MS Sql Server and I can't find much info.
Hello,So my table contains say 100,000 records, and I need to group thecategories in fld1 by the highest count of subcategories. Say fld1contains categories A, B, C, D, E.All of these categories contain subcategories AA, AB, AC, AD,...AJ, BA,BB...BJ, CA, CB, CC...CJ, etc in fld2.I am counting how many subcategories are listed for each category. LikeA may contain 5 of AA, 7 of AB, 3 of AC, 11 of AD...1 for the rest and20 of AJ. B may contain 2 of BA, 11 of BB, 7 of BC, and 1 for the rest.I want to pick up the top 3 subcategory counts for each category. Wouldlook like this:Cat SubCat CountA AJ 20A AD 11A AB 7B BB 11B BC 7B BA 2So event though each category contains 10 subcategories, I only want tolist the top 3 categories with the highest counts as above. If I justdo a group by and sort I can get this:Cat SubCat CountA ... ...AAAAAA...B ... ...BBBBB...But I just want the top 3 of each category. The only way I can think ofto do this is to query each category individually and Select Top 3, andthen Union these guys into one query. The problem is that I have tohardcode each category in the Union query. There may be new categoristhat I miss. Is there a way to achieve what I want without using Union?Thanks,Rich*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have an SSRS chart which has 4 to 5 levels of categories (for eg- Country,State,City,Street) levels , Now when i select the street it shows groups starting from Country then State, City, and then street . Because of these grouping sometimes the label is not appearing properly its gets truncated between lines , Is there any way to avoid showing the groups only show the last group for example as per the above example (Instead of showing State, City, and then street ) just show the Street itself so that all grouping lines get avoided and chart looks better without any label truncation
