Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1939
  • Last Modified:

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).
0
BeenSwank
Asked:
BeenSwank
  • 7
  • 5
1 Solution
 
BeenSwankAuthor Commented:
If required I can post up a graphical diagram of the database. For those in the know, it is futureproof.
0
 
sdstuberCommented:
I'm not entirely sure what you're looking for, but I think one of these three options should answer your question


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,
count(*) over (partition by component_status.long_name) as cnt
from (..... your joins above....)

is one way
or if you "ONLY" want the title and components and the count of each distinct pair then....

select  Title_Card.Long_name AS Title_Card_Long_name,
Component_Status.Long_name AS Component_Status_Long_name,
count(*) cnt from (....your whole query above.....)
group by Title_Card.Long_name , Component_Status.Long_name

or, if you want the title and components and count of each component irrespective of title then....

select  distinct Title_Card.Long_name AS Title_Card_Long_name,
Component_Status.Long_name AS Component_Status_Long_name,
count(*) over (partition by Component_Status.Long_name) cnt from (....your whole query above.....)

 

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Curiosity overwhelms me.  Define 'futureproof'.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BeenSwankAuthor Commented:
0
 
BeenSwankAuthor 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?
0
 
sdstuberCommented:
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.

0
 
BeenSwankAuthor Commented:
It's running on SQL 2000. Is that what you mean?
0
 
sdstuberCommented:
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
0
 
BeenSwankAuthor 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.
0
 
sdstuberCommented:
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
0
 
BeenSwankAuthor 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.
0
 
sdstuberCommented:
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
0
 
BeenSwankAuthor 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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now