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
 (Solved) Help with vote script (MS SQL)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlconfused
Yak Posting Veteran

Canada
50 Posts

Posted - 09/25/2013 :  13:51:11  Show Profile  Reply with Quote
Hello

I'm attempting to write a voting script.

I have two tables.

Table_A
username, field1, field2
========================
Joe, this, that
Henry, that, this


Table_B
nominee, vote, comment
=====================
Joe, y, sure thing
Joe, n, don't like him
Henry, y, I vote for him

Table_A will contain the names of people being voted on (username) and a few miscellaneous fields.

Table_B will contain the vote results.
Nominee will be the person voted on, vote will be "y" or "n" and a brief "comment" on the reason for the vote.

I am currently trying to display a table of all nominees
(select username from table_a)
and the number of yes and no votes beside the person's name.

As the values are in two seperate tables, I know I need to use the JOIN feature and the CASE feature but I don't know how to write the query.

Table_a username will equal Table_b nominee
and I'm looking for a recordset for yes AND no counts based on the
CASE of 'vote' column,

As always, any help would be very appreciated!

Edited by - sqlconfused on 09/25/2013 17:27:13

bitsmed
Constraint Violating Yak Guru

433 Posts

Posted - 09/25/2013 :  14:44:41  Show Profile  Reply with Quote
Maybe this:

select a.username
      ,sum(case when upper(b.vote)='Y' then 1 else 0 end) as y
      ,sum(case when upper(b.vote)='Y' then 0 else 1 end) as n
  from table_a as a
       left outer join table_b as b
                    on b.nominee=a.username
 group by a.username
Go to Top of Page

sqlconfused
Yak Posting Veteran

Canada
50 Posts

Posted - 09/25/2013 :  15:12:41  Show Profile  Reply with Quote
Thanks, it works.. almost. The 0 and 1 in the second sum should be inverted. Now I'm also looking to include the other columns from A table (a.username, a.refs, a.links)

refs are references and links are links to websites (the person provides people to vouch for him and websites with his work)

I am receiving an error:

(table) is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Edited by - sqlconfused on 09/25/2013 15:20:20
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 09/25/2013 :  15:42:47  Show Profile  Reply with Quote
The rule with using aggregation functions such as SUM,AVG etc. is that, any column that is outside of such aggregation functions must be included in the group by list. So, you might need to do the following:
select a.username,
		a.refs,
		a.links,
      ,sum(case when upper(b.vote)='Y' then 1 else 0 end) as y
      ,sum(case when upper(b.vote)='Y' then 0 else 1 end) as n
  from table_a as a
       left outer join table_b as b
                    on b.nominee=a.username
 group by a.username ,a.refs, a.links
But this can cause a problem - for example, if you have 3 links for a given user name, then where there was only one row inthe query bitsmed posted, this query would show 3 rows. If you don't want those 3 rows, then you have to again use an aggregate function on the link column and remove it from the group by list.
Go to Top of Page

sqlconfused
Yak Posting Veteran

Canada
50 Posts

Posted - 09/25/2013 :  17:27:03  Show Profile  Reply with Quote
Awesome, thank you. I feel like I should be paying for all this SQL advice.

As for the table, there will only be one column per user in Table_A where the user has set their references and website for review. It looks good so far in SQL MMC.

Edit: the second Y had to be changed to a 'N'

Edited by - sqlconfused on 09/25/2013 17:27:45
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

433 Posts

Posted - 09/25/2013 :  18:01:58  Show Profile  Reply with Quote
quote:
Originally posted by sqlconfused

Awesome, thank you. I feel like I should be paying for all this SQL advice.

As for the table, there will only be one column per user in Table_A where the user has set their references and website for review. It looks good so far in SQL MMC.

Edit: the second Y had to be changed to a 'N'


Not that it matters, as your query now seems to work the way you wanted, but had you not "inverted" the second sum logic, you didn't need to change second Y to N - in other words, you could just have left the second sum line, and it would have worked.
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.17 seconds. Powered By: Snitz Forums 2000