SQL query

5 pts.
Tags:
SQL 2008
SQL Server 2008
I have 3 columns: development no,city, status for stamp (this column is set by 0,1,2). I want to write a query that specifies percentage of stamps per city; for example percentage of stamps with 1 in city x, percentage of stamps with 2 in city x, percentage of stamps with 1 in city y....and so on. Can you advise me?

Software/Hardware used:
sql

Answer Wiki

Thanks. We'll let you know when a new response is added.
select status, city, 
COUNT(*)*100./(select count(*) 
               from tab t2 where t1.city =t2.city )
from tab t1
group by status, city

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Ben Rubenstein
    Welcome to ITKE, hengameh. Hopefully one of our members can help with answering your question soon. 

    In the meantime, make sure to fill out your profile with your picture and bio, so we can get to know a little more about you. And check out the community FAQ to learn more about us. 
    6,055 pointsBadges:
    report
  • philpl1jb
    I'm not quite sure that I understand what you expect for output.  Could you give us an example of about 6 data points and the expected output.ThanksPhil
    49,950 pointsBadges:
    report
  • TomLiotta
    Since you're using SQL Server 2008, this could be a place for a Common Table Expression (CTE) that provides total counts for each city. Then your outer SELECT can provide counts of each stamp within city. The ratio of stamp to city would be the basis of your percentages. -- Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following