We help IT Professionals succeed at work.

SQL COUNT / GROUP BY

1,955 Views
Last Modified: 2008-03-10
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).
Comment
Watch Question

Author

Commented:
If required I can post up a graphical diagram of the database. For those in the know, it is futureproof.
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Curiosity overwhelms me.  Define 'futureproof'.

Author

Commented:
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?
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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.

Author

Commented:
It's running on SQL 2000. Is that what you mean?
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Author

Commented:
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.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Author

Commented:
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.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Author

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.