SQL QUERY HELP

Question

Post reply

SQL QUERY HELP

bilal 17797

SSC Journeyman

Points: 95
More actions
July 14, 2014 at 9:05 am

#288525

Hi guys,
How can i get the total number of each distinct value in the column and write it to another table e.g.
MyTable
Id Fruit
1 Apple
2 Banana
3 Apple
4 Watermelon
5 Banana
6 Watermelon
7 Apple
Result
Fruit Count
Apple 3
Banana 2
Watermelon 2
please help
Best regards,
Bilal

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Bilal

Is this homework? What have you tried so far?

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Read up on GROUP BY and COUNT.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bilal 17797 SSC Journeyman Points: 95 More actions · Answer 3

Hi John,

As i am a beginner to SQL..

I am struggling to write a query for this. So far i managed to write a query which count distinct values in a column but i need the total number of each distinct value in the column. please if you can help me in that, will much appreciate that.

best regards,

Bilal

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

bilal 17797 (7/14/2014)
So far i managed to write a query which count distinct values in a column

Please will you share that with us. We should be able to point you in the right direction.

John

bilal 17797 SSC Journeyman Points: 95 More actions · Answer 5

DECLARE @cnt int;

SELECT @cnt = Count(Distinct Fruit) from [MyTable];

UPDATE Result SET Count=@cnt;

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Bilal

As Gail suggested, you need to use GROUP BY. It's well documented in Books Online and elsewhere. Also, what does your Result table look like?

John

bilal 17797 SSC Journeyman Points: 95 More actions · Answer 7

its Just two columns

as described in the example in my first post.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Cool. Read up (in Books Online, the help file) on GROUP BY and Count. That should set you on the right track.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bilal 17797 SSC Journeyman Points: 95 More actions · Answer 9

Hi Guys,

I had a success in what initially i was trying to do. Thanks for your help.

I have another question. Basically what i need is put results of this query into another table i have already got into respective columns.

Best regards,

Bilal

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

Bilal

That's good. It's courteous to post your solution so that those who have helped you can see what you did, and also to help anyone else who has a similar issue.

As for the second table, it depends what's already in there. You'll either want to do an INSERT, an UPDATE, or possibly a MERGE. This is why I asked you earlier what your table looks like. Maybe I should have been more specific. If you could post table DDL in the form of a CREATE TABLE statement and sample data in the form of INSERT statement(s), that will help us to help you.

John

bilal 17797 SSC Journeyman Points: 95 More actions · Answer 11

MyTable

Id Fruit

-- -----

1 Apple

2 Banana

3 Apple

4 Watermelon

5 Banana

6 Watermelon

7 Apple

==============================================

SELECT Fruit,

COUNT(*) As Cnt

FROM

MyTable

GROUP BY

Fruits

====================================================

This Generates a result table:

------------------------------

Fruits Cnt

-------- ------

1 Apples 3

2 Bananas 2

3 Watermelon 2

=======================================

Now I want to insert these results into another Table called 'Final'

like this

Final

Fruit Required Actual

---- -------- -------

Apple <user input> 3

Bananas <user input> 2

Watermelon <user input> 2

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 12

Bilal

A CREATE TABLE statement, as requested, would have been better, so that I can see whether the Required column allows NULLs. I take it there's nothing already in the Final table? You'll need to modify your SELECT query so that it returns <user input>. Then just put [font="Courier New"]INSERT INTO Final (Fruit, Required, Actual)[/font] in front of it. Alternatively (if Required does allow NULLs), you could put [font="Courier New"]INSERT INTO Final (Fruit, Actual)[/font] in front of your original query and do the update separately.

John

bilal 17797 SSC Journeyman Points: 95 More actions · Answer 13

Hi John

how can i update the final table with the result of these query because INSERT INTO command adds additional rows with same data everytime. I just want to UPDATE.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

If you want to update, then you probably want the UPDATE statement. Again well-documented in Books Online.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass