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
 General SQL Server Forums
 New to SQL Server Programming
 Count for 2 fields-Help

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 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

38200 Posts

Posted - 2014-07-30 : 19:26:56
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

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 x

JC
Go to Top of Page

jcachado
Starting Member

10 Posts

Posted - 2014-07-30 : 19:33:37
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

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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

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 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

10 Posts

Posted - 2014-07-30 : 20:20:08
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

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 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

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

jcachado
Starting Member

10 Posts

Posted - 2014-07-31 : 03:51:49
this query is great:

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

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

jcachado
Starting Member

10 Posts

Posted - 2014-07-31 : 09:53:03
Please someone suggests something on the questioned?

:(

JC
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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

- Advertisement -