Count() 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

Count() Help.

Author

Topic

GradPS
Starting Member

9 Posts

Posted - 2012-02-07 : 14:16:11

Hi there, I have two tables.

One is the inventory which holds all items
and the other is purchases which holds every transaction for an item.

I.e The Item table holds the details of Item no.1 and has an item ID column.

The second tables, holds the item_id from table 1 as a FK.

I want to COUNT all the items in the inventory, count all the times a specific item has occured in table 2 and then divide one by the other.

I know I can do

 select COUNT(DISTINCT i.itemID)
FROM item i
LEFT JOIN inventory z on i.itemID = z.itemID

This works in order to get the total number of items but I can't work out how to count the number of times a specific item(based on its ID) has been purchased, using the same query.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 14:19:46

[code]
select i.itemID,
COUNT(1)*1.0/(SELECT COUNT(DISTINCT i.itemID) FROM inventory )
FROM item i
LEFT JOIN inventory z on i.itemID = z.itemID
GROUP BY i.itemID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GradPS
Starting Member

9 Posts

Posted - 2012-02-07 : 14:29:14

Thank you, but at times I want to specify the item number. I.e I want all the rows to be counted at first(regardless of the item number, which succesfully happens) but then I only want to count the rows on the right where the item number is say 1001. The next time I may want it to be 1002. And so on.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 14:36:23

make it a procedure


CREATE PROC GetItemCount
@ItemID int = NULL
AS
select i.itemID, 
COUNT(1)*1.0/(SELECT COUNT(DISTINCT i.itemID) FROM inventory )
FROM item i
LEFT JOIN inventory z on i.itemID = z.itemID
WHERE (i.itemID  = @ItemID OR @ItemID IS NULL)
GROUP BY i.itemID

then pass like
EXEC GetItemCount 1001-- to get 1001 count
EXEC GetItemCount 1002--1002 count
EXEC GetItemCount --all count

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GradPS
Starting Member

9 Posts

Posted - 2012-02-07 : 15:02:41

it seems to be saying that there is an error with the SELECT(COUNT z.itemID) From inventory) as its returning more than one value :/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 15:17:04

quote:
Originally posted by GradPS

it seems to be saying that there is an error with the SELECT(COUNT z.itemID) From inventory) as its returning more than one value :/

it wont
i'm not sure you're not using it as suggested
can you show used query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GradPS
Starting Member

9 Posts

Posted - 2012-02-07 : 15:28:17

I think I realised what I have done, sorry. I think I worded my question wrong.

The two tables are both the same, and the link between the two tables is still the itemID. But within the second table their is a nother column which holds a random number between 1 and 10 and thats where I want to count.

I.e Table 1 has all items.
Table 2 has all transactions and the FK is the itemID from table1.

I want to return 1 result.

All of table 1's columns/ table 2 columns where the random number = 8(etc)

Sorry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 16:02:43

still not clear

post it in below format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 16:04:10

is it this then?


CREATE PROC GetItemCount
@ItemID int = NULL
AS
select i.itemID, 
COUNT(z.itemID )*1.0/COUNT(DISTINCT i.itemID) 
FROM item i
LEFT JOIN inventory z on i.itemID = z.itemID AND z.randomno=8
WHERE (i.itemID  = @ItemID OR @ItemID IS NULL)
GROUP BY i.itemID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GradPS
Starting Member

9 Posts

Posted - 2012-02-07 : 16:09:38

I want to be able to find out the total number of items available divided by the number of times a specific item(with a specific random number) has been sold.

CREATE TABLE ITEMS(ITEMID INT PK, DESCRIPTION VARCHAR(MAX)
CREATE TABLE SOLD(SOLDID INT PK, ITEMID INT FK, INT RANDOMNUMBER)

INSERT INTO ITEMS VALUES(1,"soup")
INSERT INTO ITEMS VALUES(2, "CARROTS")

INSERT INTO SOLD VALUES(1, 9)
INSERT INTO SOLD VALUES(1, 7)
INSERT INTO SOLD VALUES(1, 9)
INSERT INTO SOLD VALUES(2, 9)

I tried the query you had wrote.

So I want to find out the number of items(2), divide it by the number of times item number 1(soup) has been sold WHEN it has the random number 0.

GradPS
Starting Member

9 Posts

Posted - 2012-02-07 : 16:12:07

* Random number 9, which would be 2.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-07 : 16:15:47

clears as mud..what's the expected result set..don't post code, just what the output, based on your data should be

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 16:21:15

quote:
Originally posted by GradPS

I want to be able to find out the total number of items available divided by the number of times a specific item(with a specific random number) has been sold.

CREATE TABLE ITEMS(ITEMID INT PK, DESCRIPTION VARCHAR(MAX)
CREATE TABLE SOLD(SOLDID INT PK, ITEMID INT FK, INT RANDOMNUMBER)

INSERT INTO ITEMS VALUES(1,"soup")
INSERT INTO ITEMS VALUES(2, "CARROTS")

INSERT INTO SOLD VALUES(1, 9)
INSERT INTO SOLD VALUES(1, 7)
INSERT INTO SOLD VALUES(1, 9)
INSERT INTO SOLD VALUES(2, 9)

I tried the query you had wrote.

So I want to find out the number of items(2), divide it by the number of times item number 1(soup) has been sold WHEN it has the random number 0.


CREATE PROC GetItemCount
@ItemID int = NULL
AS
select i.itemID, 
COUNT(*) *1.0/(SELECT COUNT(DISTINCT itemID) FROM ITEMS) 
FROM ITEMS i
INNER JOIN SOLD z on i.itemID = z.itemID 
AND z.randomno=9
WHERE (i.itemID  = @ItemID OR @ItemID IS NULL)
GROUP BY i.itemID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources