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
 (Solved) Help with vote script (MS SQL)

Author  Topic 

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2013-09-25 : 13:51:11
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!

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-25 : 14:44:41
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

50 Posts

Posted - 2013-09-25 : 15:12:41
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.

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-25 : 15:42:47
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

50 Posts

Posted - 2013-09-25 : 17:27:03
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'
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-25 : 18:01:58
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
   

- Advertisement -