simple sql statement - 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

simple sql statement

Author

Topic

noobsql
Starting Member

4 Posts

Posted - 2009-07-30 : 10:21:24

I have a large table (with 1 mil+ records). Each record has the order number, items ordered and the status of the order. Order No is the primary key.

Now I want to pull out an order with more than 20 items. Here is how I am trying:

SELECT *
FROM tblOrderStatus
HAVING count(items) > 20

this doesnt work though. I am using sql 2005 server.

Any inputs on how could i get this to work?

Thanks!

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-07-30 : 10:23:09

We need more info about the table structure. Do you have an items table? If Order Number is the primary key it must be unique meaning it cannot be listed more than once in the table. So, it would make sense that your items are in a different table.

noobsql
Starting Member

4 Posts

Posted - 2009-07-30 : 10:25:18

Well there is another table which has more information about the items (pricing, barcode, brand etc) but I am just interested in pulling out orders with more than 20 items in them. What more info is needed?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-07-30 : 10:27:27

Please give us a couple of rows of data or something. Does this table actually have a field with the number of items in it?

noobsql
Starting Member

4 Posts

Posted - 2009-07-30 : 10:32:17

No I dont have a field with no of items. Here is an example:
I wrote the following query to get this:

SELECT productID, status, orderID
FROM tblOrderStatus
WHERE (orderID = 222207)

ProductID Status OrderID
4980 p 222207
4980 p 222207
4980 p 222207
4980 p 222207
6184 p 222207
NULL NULL NULL

This shows that the orderid 222207 has an order of 5 items. all with status p. I want to pull out an order with 20 or more items.

Also, 4 of these items are the same. (imagine 4 kitkat bars and 1 snickers)

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-07-30 : 10:38:38

Well, then Order No is not the primary key.

However, I believe something like this will work:

SELECT orderID
FROM tblOrderStatus
group by orderID
HAVING count(ProductID) > 20

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-07-30 : 10:42:20

Would this not work better

SELECT OrderID
FROM tblOrderStatus
GROUP BY OrderID
HAVING count(OrderID) > 20

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-07-30 : 10:43:47

sorry ignore that - DavidChel has if correct, i forgot to change the have clause to have the productid not the orderid

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 10:53:03

There must be another table that stores the items

maybe


	  SELECT OrderNo, COUNT(*) AS Items
		FROM tblOrderStatus		 a
  INNER JOIN tblOrderItems		 b	-- Just a guess
		  ON a.OrderNo = b.OrderNo
      HAVING COUNT(*) > 20
GROUP BY OrderNo

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

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 11:04:42

oooops...took to long in that meeting

Didn't know that data wasn't normalized


	  SELECT OrderNo, COUNT(*) AS Items
		FROM tblOrderStatus
      HAVING COUNT(*) > 20
GROUP BY OrderNo

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

Add yourself!
http://www.frappr.com/sqlteam

noobsql
Starting Member

4 Posts

Posted - 2009-07-30 : 11:15:26

Thanks DC, that helped me pull out orders with > 20 items. How should I change it such that it gives me orderids with 20 or more distinct items in it.

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 11:16:22

hmmmmmmm

when are these counts NOT the same?


CREATE TABLE myTable99(ProductID int, Status char(1), OrderID int)
GO

INSERT INTO myTable99(ProductID, Status, OrderID)
SELECT 4980, 'p'	 , 222207 UNION ALL
SELECT 4980, 'p'	 , 222207 UNION ALL
SELECT 4980, 'p'	 , 222207 UNION ALL
SELECT 4980, 'p'	 , 222207 UNION ALL
SELECT 6184, 'p'	 , 222207 UNION ALL
SELECT 4980, 'p'	 , 123456 UNION ALL
SELECT 4980, 'p'	 , 999999 UNION ALL
SELECT 4980, 'p'	 , 999999 UNION ALL
SELECT 4980, 'p'	 , 000000 UNION ALL
SELECT 6184, 'p'	 , 000000 UNION ALL
SELECT 4980, 'p'	 , 000000 UNION ALL
SELECT 6184, 'p'	 , 000000 UNION ALL
SELECT 4980, 'p'	 , 000000 UNION ALL
SELECT 6184, 'p'	 , 000000 UNION ALL
SELECT 4980, 'p'	 , 000000 UNION ALL
SELECT 6184, 'p'	 , 000000 UNION ALL
SELECT 4980, 'p'	 , 000000 UNION ALL
SELECT 6184, 'p'	 , 000000 UNION ALL

SELECT NULL, NULL	 , NULL
GO

SELECT * FROM myTable99

	  SELECT OrderId, COUNT(*) AS TotalItems, COUNT(OrderId), COUNT(ProductID)
		FROM myTable99
    GROUP BY OrderID


  SELECT orderID
    FROM myTable99
GROUP BY orderID
  HAVING COUNT(ProductID) > 2

DROP TABLE myTable99
GO

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

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 11:17:35

Is this a RFH?


	  SELECT OrderId, COUNT(*) AS TotalItems, COUNT(OrderId), COUNT(DISTINCT ProductID)
		FROM myTable99
    GROUP BY OrderID

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

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources