Count Query Xyntax Help 2

[AnonGod]

Hey there -

Trying to query some data and having a hard time getting my head around what needs to be done. I'm fairly sure I need a nested query, but just can't see where to put it...

Existing data sample:
[tt]
col1 col2 col3
Cat Hat Book
Big Hit Movie
Cool Beans Phrase
Cat Hat Book
Big Hit Movie
Cat Hat Book
Big Hit Movie
Cool Beans Phrase
Cat Hat Book
[/tt]

Here's the format I want the data returned in:
[tt]
col1 col2 col3 col4
Cat Hat Book 4
Big Hit Movie 3
Cool Beans Phrase 2
[/tt]

Col4 is a count column of the # of entries.

I keep thinking this is something simple, but the queries i've tried keep returning syntax errors. I'm just not getting it.

I need this in pure SQL syntax.

Any help is appreciated.

Thanks!
-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[AnonGod]

doh, nice spell check there - subject is supposed to read 'Count Query Syntax Help'

-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[jbenson001]

Select
Col1, Col2, Col3, Col4 = Count(*)
From
Table
Group By
Col1, Col2, Col3

 

[AnonGod]

Like a charm. Thanks jbenson001.

-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[jbenson001]

no problem.. glad it works

 

[AnonGod]

Maybe I can run this one by you too.
I can do it breaking it out to each column name, but it would be nice if it could do that by itself.

The first table I was querying is a voting table. I want to build a stored procedure that when an item gets enough votes to change one of the properties of a book the query will pick it up and change it.

OK, so I have the query you helped me with:

SELECT     col1_id, col2_column, col3_value, COUNT(*) AS col4_votes
FROM         dbo.voting
GROUP BY col1_id, col2_column, col3_value
HAVING      (COUNT(*) >= 5)

From the above query, I have the following sample data:
[tt]
col1_id col2_column col3_value col4_votes
66 colName Cat in the Hat 5
67 colDescription This other book is not cat in the hat 8
68 colPrice 15 6
[/tt]
I am building an update query that will live in a stored procedure and update the 'colValue' column in the dbo.voting table that actually holds the main data.

So, after all that, I think what I may need is to have the update query update the column that it queries.

Any ideas?

Thanks again.
-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[jbenson001]

What exactly are you trying to do? YOu want to update a column in what table, represnting what? Maybe a trigger is needed?

 

[AnonGod]

I was afraid that might be confusing.

I have users inserting records into a voting table. These records contain the ID, column, and new value they want in the database. When I get 5 similar records, I want to make the change that was requested.

So, If I have the following record:
colid: 66
colName: 'Dog in the Boot'
colDescription: 'This book is about a dog and a boot'

... and I get 5 votes saying that id # 66's colName column should be 'Cat in the Hat' then I want it updated.

But I don't always know what column name will be in the voting table... If I get 5 votes to update the description for book # 66, I would want that column changed.

Does that help?

Thanks
-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[amrita418]

Do you want to update the voting table where votes greater than 5?

Try:

update v
set    v.colvalue = 'whateveryouwant'
from   voting v, 
       (SELECT     col1_id, col2_column, col3_value, 
                   COUNT(*) AS col4_votes
        FROM       dbo.voting
        GROUP BY   col1_id, col2_column, col3_value
        HAVING     (COUNT(*) >= 5)) b
where   v.col1_id = b.col1_id
        and v.col2_column = b.col2.column
        and v.col3_value = b.col3_value

Regards,
AA

 

[AnonGod]

Wow amrita418 - that's a little over my head, but I'll plug and play and see how it goes. I'll let you know.

Thanks!
-andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[AnonGod]

Nope, sorry, that's not quite it.

I have a main data table and a voting table. The voting table contains votes to change a record in the main table. When there are 5 identical votes, i want the update query to update the main table.

Now, the voting table has columns for the id, column, and new value. So I can get 5 votes to change an item's name, description, price, or any other column name from the main table.

I can do it with several update queries (one for each of the column names of the main table that can be updated), but if it can be done with one, it would save a lot of time. I'm just not figuring out how the update query can read the column name from the voting table, then update that column's data in the main table.

I'm doing this now in asp/vbs, but I want to move it into a stored procedure. In asp/vbs I would pull in all id's, column name's, and new values into a record set. Then loop through the recordset creating update queries to execute. But, again, I want this to be straight SQL.

If I need to do a seperate update query for each column name, I can do that. I still figure, with my sql skills lacking, there has got to be an easier way.

Thanks for the help! Any ideas?
-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[amrita418]

Can you post the structure of the main table?

Regards,
AA

 

[amrita418]

with some sample data.

 

[AnonGod]

Sure - structure and test data
[tt]
Main Table (main):
item_id colName colDescription colPrice
66 'Cat in the Hat' 'Book about a cat and a hat' 15
67 'Gone with the Wind' 'An old movie' 20
68 'The Big Hit' 'A new movie' 22




Voting Table (voting):
vote_id voteColumnName voteNewValue
66 'colName' 'Mouse with no name'
66 'colName' 'Mouse with no name'
66 'colName' 'Mouse with no name'
66 'colName' 'Mouse with no name'
66 'colName' 'Mouse with no name'
68 'colPrice' '25'
68 'colPrice' '25'
68 'colPrice' '25'
68 'colPrice' '25'
68 'colPrice' '25'
67 'colDescription' 'The movie is not that old'
67 'colDescription' 'The movie is not that old'
[/tt]

As you can see, the voting table has 2 entries that should be changed with the query and one that only has 2 votes and should not be changed untill it has more votes.

Hope this helps

Thanks!
-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[AnonGod]

oh, the voting table has a PK column of just 'id' - the 'vote_id' is an integer value of the item_id to modify.

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[amrita418]

Here it goes:

update m
set    m.colname  = case when b.votecolumnname = 'colName' then b.voteNewValue else m.colname end,
       m.coldescription = case when b.votecolumnname = 'colDescription' then b.voteNewValue else m.coldescription end,
       m.colprice = case when b.votecolumnname = 'colPrice' then b.voteNewValue else m.colprice end
from   main m, 
       (SELECT     vote_id, votecolumnname, votenewvalue, 
                   COUNT(*) 
        FROM       dbo.voting
        GROUP BY   vote_id, votecolumnname, votenewvalue
        HAVING     (COUNT(*) >= 5)) b
where   m.item_id = b.vote_id

The code should work, but what we are doing here is:
Since we are not sure if the column to be updated is (Name, description or price) so we are updating all the 3 columns where for a given ID.
If a given colname is voted then we update colname to the new one, and we also update coldescription to the itself (no change) and price to itself.

If you do not want to do this for some reason then you have to use 3 updates.

Let me know how it goes.

Regards,
AA

 

[AnonGod]

I see. Looks like it isn't going to be as easy as I was hoping.

Well thanks for the code, you are a huge help.

I haven't decided which way to go, but it's nice to have a couple options.

Much obliged, both of you.

-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[AnonGod]

ok, working well - I'm using your code with the CASE statements.

Problem now is it errors out with "No column was specified for column 4 of 'b'."

if i pull that out into a view and then reference the view ti works fine.

Any ideas?

Thanks!
-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[AnonGod]

hold the phone - I added an 'AS' between the ')' and the table alias 'b' and all is well.

thanks again -Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[amrita418]

You actually do not need the count(*) column in the subselect.

You can either add an alias name for it or you can get rid of it alltogether.

SELECT     vote_id, votecolumnname, votenewvalue, 
           COUNT(*) 'counter' -- or remove this field
        FROM       dbo.voting
        GROUP BY   vote_id, votecolumnname, votenewvalue
        HAVING     (COUNT(*) >= 5)) b

Hope this helps.
Regards,
AA