Author |
Topic |
jcachado
Starting Member
10 Posts |
Posted - 2014-07-30 : 19:17:04
|
Hello, I need help please, I have a table named pop. This table has 2 fields, field codes with articles and other field with details of those articles. I intend to make a count to items that have more than 1 detail. In addition to this result is possible result for example:Detail articles6 45 354 1113 5232 2207the first line says I have 4 articles with 6 detailsThks!JC |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-30 : 19:26:56
|
What is your expected output given the sample data you provided?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
jcachado
Starting Member
10 Posts |
Posted - 2014-07-30 : 19:29:35
|
Hi,I meant exactly the result I showed as an example. Indicate how many articles have details xJC |
|
|
jcachado
Starting Member
10 Posts |
Posted - 2014-07-30 : 19:33:37
|
This is the table:Articles Detail2090172 12090758 12090762 12090766 12090766 22090766 32090766 42092305 12092502 12092890 12092890 22099792 12101667 12101670 12101670 22101670 3If you see the article 2101670 have 3 details and 2092890 have two.How i can do a sql to give me thar result.Thks a lot!JC |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-30 : 19:44:13
|
Then please show us expected output that matches your sample data. What you have posted doesn't seem to be related to the original post.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
jcachado
Starting Member
10 Posts |
Posted - 2014-07-30 : 20:01:15
|
Sorry but I do not understand. Have I mentioned in the last review I need, how can I get one result.how can I get in sql referring to the table I mentioned a count of the number of items and number of details.Thks!JC |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-30 : 20:06:01
|
The problem is that I can't understand what you want. You've shown expected output and sample data, but the expected output has no relation to the sample data. We need expected output for the sample data that is provided. I can only guess at this point:select article, count(*)from yourtablegroup by articlehaving count(*) > 1If that doesn't work, please post your question like this: http://www.sqlservercentral.com/articles/Best+Practices/61537/Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
jcachado
Starting Member
10 Posts |
Posted - 2014-07-30 : 20:20:08
|
Thks already help,just some another thing:select prod_id, count(*)from iprddgroup by prod_idhaving count(*) >1The result is:10053886 62324247 56 details for the product 10053886 But is possible to have a query with the result:1 product with 6 details3 products(exactly same products) with 4 detalis.You understand me?im very grateful!JC |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-30 : 20:23:00
|
I'm not understanding you. Please see the link I posted to help us help you.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
jcachado
Starting Member
10 Posts |
Posted - 2014-07-30 : 20:36:17
|
Sorry by insiste but,How many products i have in this table with 6 details?: 10012475 6 10012478 6 10012479 6 10018301 6 10053886 6 2324247 5 2324463 5 2353722 5 2443396 5Is 5 products and another 4 products with 5 details.You query is good and I thank you but is possible to show like thatproduct detail5 64 5Regards,JC |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-31 : 02:42:55
|
SELECT Detail, COUNT(*) FROM dbo.Table1 GROUP BY Detail Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
jcachado
Starting Member
10 Posts |
Posted - 2014-07-31 : 03:51:49
|
this query is great:select prod_id, count(*)from iprddgroup by prod_idhaving count(*) >2And have this result:[url]https://imagizer.imageshack.us/v2/206x162q90/661/efhQ0i.png[/url]how can I get the number of details for each prod_id?as in example 5 prod_id with 6 details. Might have to be a statement with 2 counts!!Thks for your help.Regards,JC |
|
|
jcachado
Starting Member
10 Posts |
Posted - 2014-07-31 : 09:53:03
|
Please someone suggests something on the questioned?:(JC |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-31 : 12:28:42
|
SwePeso already provided the query to give you number of details for each prod_id.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
jcachado
Starting Member
10 Posts |
Posted - 2014-07-31 : 13:38:12
|
No, in the same query no :(The perfect is the final result tell me how many prod_id have 6 details! :(JC |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-31 : 13:47:57
|
So you want SwePeso's query and the other query combined to form one result set?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
jcachado
Starting Member
10 Posts |
Posted - 2014-07-31 : 14:39:18
|
It is not possible in the same query the number of items and number of details, eg two column indicating that there is 10 prod_id with 6 details, 4 prod_id with 2 details ?Thks for your help!JC |
|
|
|