SQL Query Headache - 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 2012 Forums

Transact-SQL (2012)

SQL Query Headache

Author

Topic

David Wadsworth
Starting Member

17 Posts

Posted - 2014-09-25 : 04:02:52

Hi All,

I haven't needed to ask a question on SQL for over a year BUT I am now stumped ...

I have a table called company which contains 2 columns UIN and Value where UIN is the index and value is a company name. Another table is called contact which holds many columns, one being Company. This column contains the UIN from the company table.

I need a query which returns each UIN and Value from the company table PLUS the number of times that the UIN appears in the contact.Company table/column, so I would like to see ...

UIN, Value, Count
1, Big Co Ltd., 17
2, Tesco, 0

etc.

Obviously the simple query 'SELECT UIN, Value FROM company' works fine BUT everything else I have tried sends SQL into a nose dive and I have to restart it to recover. I have googled this and gotten hundreds of answers but none fit the bill (or even work).

Can anyone help please.

Thanks In Anticipation

David Wadsworth

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-25 : 04:06:50

[code]SELECT cm.UIN, cm.value, count(cn.Company)
from company cm join contact cn
on cm.uin = cn.company
group by cm.UIN, cm.value[/code]

Harsh Athalye
http://www.letsgeek.net/

David Wadsworth
Starting Member

17 Posts

Posted - 2014-09-25 : 08:48:25

Hi Harsh,

Thank you very much indeed for the near instantaneous answer, unfortunately I came up with the self same code and it worked just as well as yours.

Any more thoughts?

TIA

David Wadsworth

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-25 : 12:30:41

[code]
SELECT UIN,
SUM(Items) AS [Count]
FROM (
SELECT Company AS UIN,
COUNT(*) AS Items
FROM dbo.Contact
GROUP BY Company

UNION ALL

SELECT UIN,
0 AS Items
FROM dbo.Company
) AS d
GROUP BY UIN;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-25 : 12:31:18

quote:
Originally posted by David Wadsworth

Hi Harsh,

Thank you very much indeed for the near instantaneous answer, unfortunately I came up with the self same code and it worked just as well as yours.

Are you saying it doesn't work? Or it's not efficient?

For better help, you should post your question like is shown in this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

David Wadsworth
Starting Member

17 Posts

Posted - 2014-09-25 : 21:11:52

Dear Tara,

Thank you so much for your helpful reply but ... clicking on the link you provided takes me to a page where my login does not work and I cannot see the article you are pointing me to as its covered with a login window for what looks like a different forum. Do you want me to sign up for this forum just to learn how to post?

If you have a different URL that will show me what I am doing wrong then please reply OK?

I am really sorry to be a pain in the backside but I am very old and diabetic which causes my retinas to bleed and as a consequence I can only read text in a small clear circle about 5 degrees off-centre in my eye which is a bit of a sod.

Many thanks.

David Wadsworth.

David Wadsworth
Starting Member

17 Posts

Posted - 2014-09-25 : 22:07:22

Hej Peter,

Thank you for your reply.

On a vastly reduced db ...

Table - contact
Field - Company (INT)
lots of other fields of no consequence ...

Table - company
Field - UIN (index)
Field - Value (VARCHAR100)
Field - Available (bit) not relevant to this query

contact table has only 4 records
1. contact.Company = 1
2. contact.Company = 2
3. contact.Company = 3
4. contact.Company = 3

company table has 4 records
1. company.Value= 'Hein'
2. company.Value= 'Bish'
3. company.Value= 'Bosh'
4. company.Value= 'Bush'

What I want to return is ..
company.UIN, company.Value, Number of times company.UIN appears in the contact.Company field
1, Hein, 1
2, Bish, 1
3, Bosh, 2
4, Bush, 0

The best solution I have so far returns ..
1, Hein, 1
2, Bish, 1
3, Bosh, 2

This has taken me over an hour to write and I have been as verbose as I can, but if I have missed something then be a good chap and let me know. Or if I am being annoyingly dense, don't get annoyed just don't reply, I will understand, honestly. I have been coding professionally for 41 years now ( with an occasional break for sleep ha ha ) and have some understanding of SQL but being a codger I seem to annoy people.

Tack för din tid,

Pip, pip

David Wadsworth.
Apologies for the Swedish.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-26 : 12:23:49

quote:
Originally posted by David Wadsworth

Dear Tara,

Thank you so much for your helpful reply but ... clicking on the link you provided takes me to a page where my login does not work and I cannot see the article you are pointing me to as its covered with a login window for what looks like a different forum. Do you want me to sign up for this forum just to learn how to post?

If you have a different URL that will show me what I am doing wrong then please reply OK?

I don't have a different URL. That link has the most thorough and helpful article on how to post a question to get better and fast help. Yes you will need to signup on their site to be able to view that article. I hate that it's required, but that's how they have it setup.

Following the instructions in that link allows us to duplicate your issue on our own machines and then work on a solution. You'll often have many replies because people are very willing to work on problems when they can test it out locally. It's harder for us when we have to visualize the issue and read into the posts when there could be missing information.

Actually I do have a link. Here's a question I posted many years ago on here where I needed help. Notice the info I provided.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42516

A few years later, I needed more help with that same issue and posted again.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110247

Notice how many replies and solutions I got in both links. This was because of how I structured my post, providing the CREATE TABLE statement, INSERT INTO statements for sample data, and then the expected result set using that sample data. This is exactly what we need.

Having said all of that, SwePeso or someone else will likely be able to help out given the info in your last post.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources