select statement help! - SQL Server Forums

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

select statement help!

Author

Topic

bismarkcount
Starting Member

15 Posts

Posted - 2007-11-05 : 16:07:55

Hello!! i need some help in building a sql server select statement:

i have a table with these columns and info:

product_id | quantity
------------------------
prod1 | 2
prod1 | 4
prod2 | 4
prod3 | 3
prod2 | 2
prod2 | 1

i need to build a statement in order to have each product and how many items of the product i have in existence (sum of quantities of each occurrence of a product). so the query result would look like this:

product_id | quantity
------------------------
prod1 | 6
prod2 | 7
prod3 | 3

any help will be appreciated.

thnx!!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-05 : 16:17:41

SELECT Product_Id, Count(*) As Quantity
FROM PRoducts
GROUP BY PRoduct_ID

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-05 : 16:17:59

Have a look at the group by clause.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sshelper
Posting Yak Master

216 Posts

Posted - 2007-11-05 : 16:18:38

Instead of a COUNT, it should be SUM:

SELECT Product_Id, SUM(Quantity) As Quantity
FROM PRoducts
GROUP BY PRoduct_ID

SQL Server Helper
http://www.sql-server-helper.com

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-05 : 16:19:28

quote:
Originally posted by sshelper

Instead of a COUNT, it should be SUM:

SELECT Product_Id, SUM(Quantity) As Quantity
FROM PRoducts
GROUP BY PRoduct_ID

SQL Server Helper
http://www.sql-server-helper.com

oops...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

bismarkcount
Starting Member

15 Posts

Posted - 2007-11-05 : 16:20:57

thnx a lot!! it worked fine for me!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-06 : 00:54:25

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-06 : 08:12:07

quote:
Originally posted by dinakar

SELECT Product_Id, Count(*) As Quantity
FROM PRoducts
GROUP BY PRoduct_ID

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

hi dinaker
acc to question i think
instead of count(*) sum(quantity) is prefferable
what u say

select product,sum(quantity) from table group by product

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-06 : 08:14:08

quote:
Originally posted by arorarahul.0688

quote:
Originally posted by dinakar

SELECT Product_Id, Count(*) As Quantity
FROM PRoducts
GROUP BY PRoduct_ID

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

hi dinaker
acc to question i think
instead of count(*) sum(quantity) is prefferable
what u say

select product,sum(quantity) from table group by product

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Again, are you reading the replies before you reply?

Madhivanan

Failing to plan is Planning to fail

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-06 : 08:38:58

quote:
Originally posted by madhivanan

quote:
Originally posted by arorarahul.0688

quote:
Originally posted by dinakar

SELECT Product_Id, Count(*) As Quantity
FROM PRoducts
GROUP BY PRoduct_ID

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

hi dinaker
acc to question i think
instead of count(*) sum(quantity) is prefferable
what u say

select product,sum(quantity) from table group by product

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Again, are you reading the replies before you reply?

Madhivanan

Failing to plan is Planning to fail

its not a case accidently i just look at the first rply of dinaker and gave my views
u felt hearted as my answer just added another entry for that i am so sorry
and thanks for a rply in which i made a little mistake and u rectified that

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-06 : 08:40:54

quote:
Originally posted by arorarahul.0688

quote:
Originally posted by madhivanan

quote:
Originally posted by arorarahul.0688

quote:
Originally posted by dinakar

SELECT Product_Id, Count(*) As Quantity
FROM PRoducts
GROUP BY PRoduct_ID

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

hi dinaker
acc to question i think
instead of count(*) sum(quantity) is prefferable
what u say

select product,sum(quantity) from table group by product

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Again, are you reading the replies before you reply?

Madhivanan

Failing to plan is Planning to fail

its not a case accidently i just look at the first rply of dinaker and gave my views
u felt hearted as my answer just added another entry for that i am so sorry
and thanks for a rply in which i made a little mistake and u rectified that

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Well. It is good practice to reply after reading all the replies posted

Madhivanan

Failing to plan is Planning to fail

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-07 : 00:47:15

quote:
Originally posted by madhivanan

quote:
Originally posted by arorarahul.0688

quote:
Originally posted by madhivanan

quote:
Originally posted by arorarahul.0688

quote:
Originally posted by dinakar

SELECT Product_Id, Count(*) As Quantity
FROM PRoducts
GROUP BY PRoduct_ID

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

hi dinaker
acc to question i think
instead of count(*) sum(quantity) is prefferable
what u say

select product,sum(quantity) from table group by product

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Again, are you reading the replies before you reply?

Madhivanan

Failing to plan is Planning to fail

its not a case accidently i just look at the first rply of dinaker and gave my views
u felt hearted as my answer just added another entry for that i am so sorry
and thanks for a rply in which i made a little mistake and u rectified that

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Well. It is good practice to reply after reading all the replies posted

Madhivanan

Failing to plan is Planning to fail

well i just looks for problems and tries to sort out that i am a fresher in sql server, so when i saw a wrong answer before rply i told him. well if u think i read replies before reading then i must say its only ur thnking.
i take every query as a fresh problem and try to solve it and quite possible two persons having same answer ok

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-11-07 : 14:36:23

quote:
[i]
well i just looks for problems and tries to sort out that i am a fresher in sql server, so when i saw a wrong answer before rply i told him. well if u think i read replies before reading then i must say its only ur thnking.
i take every query as a fresh problem and try to solve it and quite possible two persons having same answer ok

But most of the time you're not even reading the question entirely, and so the answer you post is simply wrong. By reading other answers first, you might actually realize your mistake before you make it.

And anyone can tell you, it's better to think before you speak; this applies to answering forum questions as well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources