Link to home
Create AccountLog in
Avatar of BeenSwank
BeenSwank

asked on

SQL COUNT / GROUP BY

I have built the following query:

SELECT
Component_Status.Long_name AS Component_Status_Long_name,  
Order_Insert.Insert_Date AS Order_Insert_Insert_Date,  
Order_Record.URN_number AS Order_Record_URN_number,  
Title_Card.Long_name AS Title_Card_Long_name

FROM
(Order_Text INNER JOIN  
(Title_Card INNER JOIN
(Order_Record INNER JOIN
(Order_Insert INNER JOIN Order_Row ON Order_Insert.Order_Row_ID = Order_Row.Order_Row_ID)  ON Order_Record.Order_ID = Order_Row.Order_ID)  ON
Title_Card.Title_ID = Order_Row.Title_ID)  ON
Order_Text.Order_Text_ID = Order_Row.Order_Text_ID)  INNER JOIN  
(Component_Status INNER JOIN Component_Usage ON Component_Status.Component_Status_ID = Component_Usage.Component_Status_ID) ON
Order_Text.Order_Text_ID = Component_Usage.Order_Text_ID

WHERE
Insert_Date = ? AND Stop_date IS NOT NULL

ORDER BY
Title_Card.Long_name ASC,
Order_Insert.Insert_Date ASC,
Component_Status.Long_Name ASC,
Order_Record.URN_number ASC;

This generates all the records but the problem is I then need to summarise them. I need to list each Title_Card.Long_name (this is a Newspaper Title) with a count of all the Components (adverts) that are either 'Completed', 'In Progress', 'Not Started' etc one of these will be listed in Component_Status.Long_Name, then output them in html/asp/vbscript (via dreamweaver).
Avatar of BeenSwank
BeenSwank

ASKER

If required I can post up a graphical diagram of the database. For those in the know, it is futureproof.
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Curiosity overwhelms me.  Define 'futureproof'.
how do i output that in html? my knowledge of actually executing code is limited to built in wizards and GUI's of dreamweaver and access. I usually load the data into a recordset, how would a single value returned differ from this?
the sql snippets I sent above should be usable in a recordset just as you normally would use any other SQL.  I will throw one caveat though.  The "partition by" syntax I used in two of the examples if for Oracle.  You didn't specify what your database was so maybe that won't work.

It's running on SQL 2000. Is that what you mean?
yep, that's what I meant.  unfortunately that means analytics (the "partition by" syntax) aren't available to you  so hopefully my 2nd suggestion will work since that doesn't use them
The idea is to give a count based on the adverts for a particular newspaper that are either completed, in progess or being proofed. So it would say something like:

The Somewhere Observer
140 of 1800 adverts completed (7.7%)
933 of 1800 adverts proofed (51.8%)
243 of 1800 adverts in progress (13.5%)
484 of 1800 adverts not started etc

This gives the production managers an overview of typesetter crew's performance. I would do the math through VBScript with some kind of flash graph.
ok then my second option should work just fine for you then.

It'll give you the number of adverts per title with each status.
Then in your vb script you can sum them and generate your percentages
SELECT
Title_Card.Long_name AS Title_Card_Long_name,
Component_Status.Long_name AS Component_Status_Long_name,
count(*) cnt from (
SELECT
Component_Status.Long_name AS Component_Status_Long_name,  Order_Insert.Insert_Date AS Order_Insert_Insert_Date,  Order_Record.URN_number AS Order_Record_URN_number,  Title_Card.Long_name AS Title_Card_Long_name
FROM
(Order_Text INNER JOIN  
(Title_Card INNER JOIN
(Order_Record INNER JOIN
(Order_Insert INNER JOIN Order_Row ON Order_Insert.Order_Row_ID = Order_Row.Order_Row_ID)  ON Order_Record.Order_ID = Order_Row.Order_ID)  ON
Title_Card.Title_ID = Order_Row.Title_ID)  ON
Order_Text.Order_Text_ID = Order_Row.Order_Text_ID)  INNER JOIN  
(Component_Status INNER JOIN
Component_Usage ON Component_Status.Component_Status_ID = Component_Usage.Component_Status_ID) ON
Order_Text.Order_Text_ID = Component_Usage.Order_Text_ID

WHERE
Insert_Date = MMColParam AND Stop_date IS NOT NULL

ORDER BY
Title_Card.Long_name ASC, Order_Insert.Insert_Date ASC, Component_Status.Long_Name ASC, Order_Record.URN_number ASC) group by Title_Card.Long_name , Component_Status.Long_name

--------------------

results in this error:

[Microsoft][ODBC SQL Server Driver][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
take out the order by clause from your original query, and move it outside the parentheses.  You'll have to use the aliased columns then
Sorry can you please check this, its giving me a syntax error by GROUP:

SELECT
Title_Card.Long_name AS Title_Card_Long_name,  
Component_Status.Long_name AS Component_Status_Long_name,  

count(*) cnt from (

SELECT  
Component_Status.Long_name AS Component_Status_Long_name,  
Order_Insert.Insert_Date AS Order_Insert_Insert_Date,  
Order_Record.URN_number AS Order_Record_URN_number,  
Title_Card.Long_name AS Title_Card_Long_name

FROM
(Order_Text INNER JOIN  
 (Title_Card INNER JOIN  
(Order_Record INNER JOIN  
(Order_Insert INNER JOIN Order_Row ON Order_Insert.Order_Row_ID = Order_Row.Order_Row_ID)  ON Order_Record.Order_ID = Order_Row.Order_ID)  
ON  Title_Card.Title_ID = Order_Row.Title_ID)  
ON  Order_Text.Order_Text_ID = Order_Row.Order_Text_ID)  
INNER JOIN  
(Component_Status INNER JOIN  Component_Usage ON Component_Status.Component_Status_ID = Component_Usage.Component_Status_ID)
ON  Order_Text.Order_Text_ID = Component_Usage.Order_Text_ID

WHERE
Insert_Date = MMColParam AND Stop_date IS NOT NULL)

group by Title_Card.Long_name , Component_Status.Long_name