SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count for 2 fields-Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jcachado
Starting Member

Portugal
10 Posts

Posted - 07/30/2014 :  19:17:04  Show Profile  Reply with Quote
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 articles
6 4
5 35
4 111
3 523
2 2207

the first line says I have 4 articles with 6 details

Thks!




JC

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 07/30/2014 :  19:26:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
What is your expected output given the sample data you provided?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jcachado
Starting Member

Portugal
10 Posts

Posted - 07/30/2014 :  19:29:35  Show Profile  Reply with Quote
Hi,

I meant exactly the result I showed as an example.

Indicate how many articles have details x

JC
Go to Top of Page

jcachado
Starting Member

Portugal
10 Posts

Posted - 07/30/2014 :  19:33:37  Show Profile  Reply with Quote
This is the table:

Articles Detail
2090172 1
2090758 1
2090762 1
2090766 1
2090766 2
2090766 3
2090766 4
2092305 1
2092502 1
2092890 1
2092890 2
2099792 1
2101667 1
2101670 1
2101670 2
2101670 3

If 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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 07/30/2014 :  19:44:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jcachado
Starting Member

Portugal
10 Posts

Posted - 07/30/2014 :  20:01:15  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 07/30/2014 :  20:06:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 yourtable
group by article
having count(*) > 1

If that doesn't work, please post your question like this: http://www.sqlservercentral.com/articles/Best+Practices/61537/



Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jcachado
Starting Member

Portugal
10 Posts

Posted - 07/30/2014 :  20:20:08  Show Profile  Reply with Quote
Thks already help,just some another thing:

select prod_id, count(*)
from iprdd
group by prod_id
having count(*) >1

The result is:
10053886 6
2324247 5
6 details for the product 10053886

But is possible to have a query with the result:

1 product with 6 details
3 products(exactly same products) with 4 detalis.

You understand me?

im very grateful!



JC
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 07/30/2014 :  20:23:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
I'm not understanding you. Please see the link I posted to help us help you.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jcachado
Starting Member

Portugal
10 Posts

Posted - 07/30/2014 :  20:36:17  Show Profile  Reply with Quote
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 5

Is 5 products and another 4 products with 5 details.

You query is good and I thank you but is possible to show like that

product detail
5 6
4 5

Regards,

JC
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 07/31/2014 :  02:42:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT Detail, COUNT(*) FROM dbo.Table1 GROUP BY Detail


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

jcachado
Starting Member

Portugal
10 Posts

Posted - 07/31/2014 :  03:51:49  Show Profile  Reply with Quote
this query is great:

select prod_id, count(*)
from iprdd
group by prod_id
having count(*) >2

And have this result:

https://imagizer.imageshack.us/v2/206x162q90/661/efhQ0i.png

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
Go to Top of Page

jcachado
Starting Member

Portugal
10 Posts

Posted - 07/31/2014 :  09:53:03  Show Profile  Reply with Quote
Please someone suggests something on the questioned?

:(

JC
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 07/31/2014 :  12:28:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
SwePeso already provided the query to give you number of details for each prod_id.


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jcachado
Starting Member

Portugal
10 Posts

Posted - 07/31/2014 :  13:38:12  Show Profile  Reply with Quote
No, in the same query no :(

The perfect is the final result tell me how many prod_id have 6 details!

:(

JC
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 07/31/2014 :  13:47:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
So you want SwePeso's query and the other query combined to form one result set?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jcachado
Starting Member

Portugal
10 Posts

Posted - 07/31/2014 :  14:39:18  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000