Counting Rows

[milton747]

I'm hoping this is simple.
I have a list categories, A,B C. I want to get the count of the number of occurances each category appears in another table.

Result Wanted:
Category COUNT_OF_LINES
A 10
B 12
C 8

Psudeo SQL
SELECT CATEGORY
(SELECT COUNT_OF_LINES
FROM ORDERS_LIST) AS OL
WHERE OL.CATEGORY = A or B or C

Any help. Does it need a sub-query.

I suspect I will have to read the categories from a table soon. Same result needed but:


Psudeo SQL
SELECT CATEGORY FROM CATEGORY_TABLE DISTINCT

(SELECT COUNT_OF_LINES
FROM ORDERS_LIST) AS OL
WHERE OL.CATEGORY = CATEGORY

Yep, I'm new at this.

 

[Golom]

Select Category, Count(*) As [Category Count]

From TheOtherTable

Where Category IN ("A", "B", "C")

Group By Category

If you just want those three categories. Remove the Where clause to see the result for all categories in the table.

 

[lespaul]

I think this is what you're asking for:

SELECT Category, COUNT(*) As Count_Of_Lines FROM TableName WHERE Category in ("A", "B", "C") GROUP BY Category

reading the categories, maybe?

SELECT CategoryName, COUNT(*) FROM TableName INNER JOIN CategoryTable ON TableName.Category = CategoryTable.Category WHERE TableName.Category IN ("A", "B", "C") GROUP BY CategoryName

not really clear what you're asking for. If the only categories are A B and C then you don't need the where clause. If you need the description of the category from a secondary table you would use the second query.

HTH


Leslie

Have you met Hardy Heron?

 

[Remou]

How about:

SELECT Category, Count(Category)
FROM OtherTable
GROUP BY Category
HAVING Category IN ('A','B','C')

 

[milton747]

Almost. Below runs, but...
It shows orders ID and the count of rows for each category. But the count is way off.
I also want to show a row with a count of zero, if an order has no rows for say 'Static Ram'


Select
orders.orderID,
items.Category,
Count(*) As CATCOUNT
From items, orders
Where items.Category IN ( 'Controller','Driver','DSP','Dynamic Ram','Processor', 'Static Ram' )
Group By orders.orderID, items.Category

 

[milton747]

IGNORE LAST POST...the count is good now.
Down to showing a row with a count of zero if there are no rows for say Static Ram for a particular order.

Select
orders.orderID,
items.Category,
Count(*) As CATCOUNT
From items, orders
Where items.Category IN ( 'Controller','Driver','DSP','Dynamic Ram','Processor', 'Static Ram' )
AND items.orderID = orders.orderID
Group By orders.orderID, items.Category

 

[lespaul]

the issue is the "INNER JOIN"....maybe read Understanding SQL Joins

try this (assuming Items has ALL the categories):

Select
orders.orderID,
items.Category,
Count(*) As CATCOUNT
From items i 
LEFT JOIN orders o on i.orderid = orders.orderid
 Where items.Category IN  ( 'Controller','Driver','DSP','Dynamic Ram','Processor', 'Static Ram' )
Group By orders.orderID, items.Category

have to say i'm confused as to why orderid is in items, but if it works for you....
HTH

Leslie

 

[milton747]

Hi Leslie,

Thanks for reply.
Engine says there's a syntax error in JOIN operation
I'll play with it.

<<i'm confused as to why orderid is in items, but if it works for you>>
Yeah, it is a bit odd. I didn't set up the schemas. I just work with what's there and hope.

 

[lespaul]

try this:

Select
orders.orderID,
items.Category,
Count(*) As CATCOUNT
From items i
LEFT JOIN orders o on i.orderid = o.orderid
 Where items.Category IN  ( 'Controller','Driver','DSP','Dynamic Ram','Processor', 'Static Ram' )
Group By orders.orderID, items.Category

I didn't use the alias O in the second part of the join...maybe this will work...
Leslie

 

[milton747]

Getting there.
Few Params. Expected 2.
(Oh brother, if I had dime for the times I got that, I'd be retired.)

 

[PHV]

What about this ?

SELECT O.orderID, I.Category, Count(*) As CATCOUNT
FROM items I
LEFT JOIN orders O ON I.orderID = O.orderID
WHERE I.Category IN ('Controller','Driver','DSP','Dynamic Ram','Processor','Static Ram')
GROUP BY O.orderID, I.Category

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[milton747]

Works good. Thanks.
I can work with it from here.
I dont' want to reouble folks.

Maybe it's not possible what I want to do.
Given a fixed list of categories, get a count, even zero for each order. Something like:

OID CAT COUNT
1075 Static Ram 4
1075 DSP 0
1075 Controller 2
1080 Static Ram 0
1080 DSP 1
1080 Controller 3

etc....

 

[PHV]

Something like this ?

SELECT O.orderID AS OID, 'Controller' AS CAT, Nz(I.N,0) AS [COUNT] FROM orders O LEFT JOIN (
SELECT orderID,Count(*) AS N FROM items WHERE Category='Controller' GROUP BY orderID
) I ON O.orderID=I.orderID
UNION SELECT O.orderID, 'Driver', Nz(I.N,0) FROM orders O LEFT JOIN (
SELECT orderID,Count(*) AS N FROM items WHERE Category='Driver' GROUP BY orderID
) I ON O.orderID=I.orderID
UNION SELECT O.orderID, 'DSP', Nz(I.N,0) FROM orders O LEFT JOIN (
SELECT orderID,Count(*) AS N FROM items WHERE Category='DSP' GROUP BY orderID
) I ON O.orderID=I.orderID
UNION SELECT O.orderID, 'Dynamic Ram', Nz(I.N,0) FROM orders O LEFT JOIN (
SELECT orderID,Count(*) AS N FROM items WHERE Category='Dynamic Ram' GROUP BY orderID
) I ON O.orderID=I.orderID
UNION SELECT O.orderID, 'Processor', Nz(I.N,0) FROM orders O LEFT JOIN (
SELECT orderID,Count(*) AS N FROM items WHERE Category='Processor' GROUP BY orderID
) I ON O.orderID=I.orderID
UNION SELECT O.orderID, 'Static Ram', Nz(I.N,0) FROM orders O LEFT JOIN (
SELECT orderID,Count(*) AS N FROM items WHERE Category='Static Ram' GROUP BY orderID
) I ON O.orderID=I.orderID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886