Good case for COUNT ?? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Good case for COUNT ??

Author

Topic

cash
Starting Member

6 Posts

Posted - 2008-10-11 : 22:28:43

Hi, Im working on a project and ran into a small problem.
Check out this scenario:

SELECT
tbl_cart.ship_ID,
tbl_products.product_Name
FROM
tbl_cart, tbl_products
WHERE
tbl_cart.sku_ID = tbl_products.SKU_ID
ORDER BY
tbl_cart.ship_ID

returns:

ship ID product name
1 12 item A
2 12 item C
3 30 item B
4 30 item B

How can i format the query so that my results look like this instead:

ship ID product name qty
1 12 item A 1
2 12 item C 1
3 30 item B 2

I'd sure appreciate some help on his one! Thanks!

-Cash

cash
Starting Member

6 Posts

Posted - 2008-10-11 : 23:02:50

Im still pretty new to SQL, sorry if this was a stupid question.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111752

Not quite what I was looking for, but it's gives me a bit of direction... it think.

cash
Starting Member

6 Posts

Posted - 2008-10-11 : 23:40:51

something using syntax like:

SELECT COUNT(DISTINCT) AS FROM

?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-12 : 02:18:23

[code]SELECT
tbl_cart.ship_ID,
tbl_products.product_Name,
COUNT(*) AS Qty
FROM
tbl_cart, tbl_products
WHERE
tbl_cart.sku_ID = tbl_products.SKU_ID
GROUP BY tbl_cart.ship_ID,
tbl_products.product_NameORDER BY tbl_cart.ship_ID[/code]

EDIT:had missed a space

cash
Starting Member

6 Posts

Posted - 2008-10-12 : 14:22:03

Thanks visakh16!

Unfortunately, Im getting an error:

Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tbl_products.product_Name COUNT(*)'.

Trying to figure out why..

EDIT:

OK, That was stupid, it was a missing comma after 'tbl_products.product_Name'

Thanks again for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-12 : 14:30:15

quote:
Originally posted by cash

Thanks visakh16!

Unfortunately, Im getting an error:

Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tbl_products.product_Name COUNT(*)'.

Trying to figure out why..

EDIT:

OK, That was stupid, it was a missing comma after 'tbl_products.product_Name'

Thanks again for your help!

You're welcome

Glad tht i could help you out

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-14 : 03:56:18

quote:
Originally posted by cash

Thanks visakh16!

Unfortunately, Im getting an error:

Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tbl_products.product_Name COUNT(*)'.

Trying to figure out why..

EDIT:

OK, That was stupid, it was a missing comma after 'tbl_products.product_Name'

Thanks again for your help!

You should post Access Questions in Access Forum

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources