I'm okay with simple select statements, but this one is kicking my behind...
I'm trying to create an overview of our data holdings for various
categories. It needs to be dynamic as the types of data and the accompanying
criteria will be dynamic.
The data holdings table, which I'll call "DH", is related via a one-to-many
to an "Xref_DH_Topic" table, which is related via a many-to-one to a "Topics"
table.
"DH" table:
| dhid | Name |
| 1 | Fred's Ocean Carbon Data |
| 2 | Joe's Tide Data |
| 3 | Pete's Surface Flux Data |
| 4 | Lou's Tide & Surface Flux Data |
"Xref_DH_Topic" table:
| xid | dhid | topicid |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 3 |
| 5 | 3 | 1 |
| 6 | 3 | 4 |
| 7 | 3 | 5 |
| 8 | 4 | 1 |
| 9 | 4 | 3 |
| 10 | 4 | 4 |
| 11 | 4 | 5 |
"Topics" table:
| topicid | TopicTitle |
| 1 | Ocean |
| 2 | Carbon |
| 3 | Tide |
| 4 | Surface |
| 5 | Flux |
So far so good. Now I want to have a "DH Matrix" table which defines the
criteria for inclusion in a particular row, which will be modified as more
data holdings and topics are added. It is linked to an "Xref_Matrix_Topic"
table which links to the same "Topics" table as above.
"DH Matrix" table:
| dhmid | Category |
| 1 | All Ocean Data |
| 2 | Tide Data Holdings |
| 3 | Carbon Data Holdings |
| 4 | Surface Flux Data Holdings |
"Xref_Matrix_Topic" table:
| xmtid | dhmid | topicid |
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 3 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
| 6 | 4 | 1 |
| 7 | 4 | 4 |
| 8 | 4 | 5 |
The desired end-result is a view that will display each "category" in the
"DH Matrix" table and count up the number of rows in the "DH" table that
match the criteria set up in the "Xref_Matrix_Topic" table.
Matrix of Data Holdings:
| Category | # of Hits in DH |
| All Ocean Data | 4 |
| Tide Data Holdings | 2 |
| Carbon Data Holdings | 1 |
| Surface Flux Holdings | 2 |
Any help would be greatly appreciated.
TIA.
What about some DDL scripts and data for us ?
Showing posts with label overview. Show all posts
Showing posts with label overview. Show all posts
Thursday, March 29, 2012
Count of matching result sets
I'm okay with simple select statements, but this one is kicking my behind...
I'm trying to create an overview of our data holdings for various
categories. It needs to be dynamic as the types of data and the accompanying
criteria will be dynamic.
The data holdings table, which I'll call "DH", is related via a one-to-many
to an "Xref_DH_Topic" table, which is related via a many-to-one to a "Topics"
table.
"DH" table:
| dhid | Name |
| 1 | Fred's Ocean Carbon Data |
| 2 | Joe's Tide Data |
| 3 | Pete's Surface Flux Data |
| 4 | Lou's Tide & Surface Flux Data |
"Xref_DH_Topic" table:
| xid | dhid | topicid |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 3 |
| 5 | 3 | 1 |
| 6 | 3 | 4 |
| 7 | 3 | 5 |
| 8 | 4 | 1 |
| 9 | 4 | 3 |
| 10 | 4 | 4 |
| 11 | 4 | 5 |
"Topics" table:
| topicid | TopicTitle |
| 1 | Ocean |
| 2 | Carbon |
| 3 | Tide |
| 4 | Surface |
| 5 | Flux |
So far so good. Now I want to have a "DH Matrix" table which defines the
criteria for inclusion in a particular row, which will be modified as more
data holdings and topics are added. It is linked to an "Xref_Matrix_Topic"
table which links to the same "Topics" table as above.
"DH Matrix" table:
| dhmid | Category |
| 1 | All Ocean Data |
| 2 | Tide Data Holdings |
| 3 | Carbon Data Holdings |
| 4 | Surface Flux Data Holdings |
"Xref_Matrix_Topic" table:
| xmtid | dhmid | topicid |
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 3 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
| 6 | 4 | 1 |
| 7 | 4 | 4 |
| 8 | 4 | 5 |
The desired end-result is a view that will display each "category" in the
"DH Matrix" table and count up the number of rows in the "DH" table that
match the criteria set up in the "Xref_Matrix_Topic" table.
Matrix of Data Holdings:
| Category | # of Hits in DH |
| All Ocean Data | 4 |
| Tide Data Holdings | 2 |
| Carbon Data Holdings | 1 |
| Surface Flux Holdings | 2 |
Any help would be greatly appreciated.
TIA.What about some DDL scripts and data for us ?sql
I'm trying to create an overview of our data holdings for various
categories. It needs to be dynamic as the types of data and the accompanying
criteria will be dynamic.
The data holdings table, which I'll call "DH", is related via a one-to-many
to an "Xref_DH_Topic" table, which is related via a many-to-one to a "Topics"
table.
"DH" table:
| dhid | Name |
| 1 | Fred's Ocean Carbon Data |
| 2 | Joe's Tide Data |
| 3 | Pete's Surface Flux Data |
| 4 | Lou's Tide & Surface Flux Data |
"Xref_DH_Topic" table:
| xid | dhid | topicid |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 3 |
| 5 | 3 | 1 |
| 6 | 3 | 4 |
| 7 | 3 | 5 |
| 8 | 4 | 1 |
| 9 | 4 | 3 |
| 10 | 4 | 4 |
| 11 | 4 | 5 |
"Topics" table:
| topicid | TopicTitle |
| 1 | Ocean |
| 2 | Carbon |
| 3 | Tide |
| 4 | Surface |
| 5 | Flux |
So far so good. Now I want to have a "DH Matrix" table which defines the
criteria for inclusion in a particular row, which will be modified as more
data holdings and topics are added. It is linked to an "Xref_Matrix_Topic"
table which links to the same "Topics" table as above.
"DH Matrix" table:
| dhmid | Category |
| 1 | All Ocean Data |
| 2 | Tide Data Holdings |
| 3 | Carbon Data Holdings |
| 4 | Surface Flux Data Holdings |
"Xref_Matrix_Topic" table:
| xmtid | dhmid | topicid |
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 3 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
| 6 | 4 | 1 |
| 7 | 4 | 4 |
| 8 | 4 | 5 |
The desired end-result is a view that will display each "category" in the
"DH Matrix" table and count up the number of rows in the "DH" table that
match the criteria set up in the "Xref_Matrix_Topic" table.
Matrix of Data Holdings:
| Category | # of Hits in DH |
| All Ocean Data | 4 |
| Tide Data Holdings | 2 |
| Carbon Data Holdings | 1 |
| Surface Flux Holdings | 2 |
Any help would be greatly appreciated.
TIA.What about some DDL scripts and data for us ?sql
Count of matching result sets
I'm okay with simple select statements, but this one is kicking my behind...
I'm trying to create an overview of our data holdings for various
categories. It needs to be dynamic as the types of data and the accompanyin
g
criteria will be dynamic.
The data holdings table, which I'll call "DH", is related via a one-to-many
to an "Xref_DH_Topic" table, which is related via a many-to-one to a "Topics
"
table.
"DH" table:
| dhid | Name |
| 1 | Fred's Ocean Carbon Data |
| 2 | Joe's Tide Data |
| 3 | Pete's Surface Flux Data |
| 4 | Lou's Tide & Surface Flux Data |
"Xref_DH_Topic" table:
| xid | dhid | topicid |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 3 |
| 5 | 3 | 1 |
| 6 | 3 | 4 |
| 7 | 3 | 5 |
| 8 | 4 | 1 |
| 9 | 4 | 3 |
| 10 | 4 | 4 |
| 11 | 4 | 5 |
"Topics" table:
| topicid | TopicTitle |
| 1 | Ocean |
| 2 | Carbon |
| 3 | Tide |
| 4 | Surface |
| 5 | Flux |
So far so good. Now I want to have a "DH Matrix" table which defines the
criteria for inclusion in a particular row, which will be modified as more
data holdings and topics are added. It is linked to an "Xref_Matrix_Topic"
table which links to the same "Topics" table as above.
"DH Matrix" table:
| dhmid | Category |
| 1 | All Ocean Data |
| 2 | Tide Data Holdings |
| 3 | Carbon Data Holdings |
| 4 | Surface Flux Data Holdings |
"Xref_Matrix_Topic" table:
| xmtid | dhmid | topicid |
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 3 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
| 6 | 4 | 1 |
| 7 | 4 | 4 |
| 8 | 4 | 5 |
The desired end-result is a view that will display each "category" in the
"DH Matrix" table and count up the number of rows in the "DH" table that
match the criteria set up in the "Xref_Matrix_Topic" table.
Matrix of Data Holdings:
| Category | # of Hits in DH |
| All Ocean Data | 4 |
| Tide Data Holdings | 2 |
| Carbon Data Holdings | 1 |
| Surface Flux Holdings | 2 |
Any help would be greatly appreciated.
TIA.What about some DDL scripts and data for us ?
I'm trying to create an overview of our data holdings for various
categories. It needs to be dynamic as the types of data and the accompanyin
g
criteria will be dynamic.
The data holdings table, which I'll call "DH", is related via a one-to-many
to an "Xref_DH_Topic" table, which is related via a many-to-one to a "Topics
"
table.
"DH" table:
| dhid | Name |
| 1 | Fred's Ocean Carbon Data |
| 2 | Joe's Tide Data |
| 3 | Pete's Surface Flux Data |
| 4 | Lou's Tide & Surface Flux Data |
"Xref_DH_Topic" table:
| xid | dhid | topicid |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 3 |
| 5 | 3 | 1 |
| 6 | 3 | 4 |
| 7 | 3 | 5 |
| 8 | 4 | 1 |
| 9 | 4 | 3 |
| 10 | 4 | 4 |
| 11 | 4 | 5 |
"Topics" table:
| topicid | TopicTitle |
| 1 | Ocean |
| 2 | Carbon |
| 3 | Tide |
| 4 | Surface |
| 5 | Flux |
So far so good. Now I want to have a "DH Matrix" table which defines the
criteria for inclusion in a particular row, which will be modified as more
data holdings and topics are added. It is linked to an "Xref_Matrix_Topic"
table which links to the same "Topics" table as above.
"DH Matrix" table:
| dhmid | Category |
| 1 | All Ocean Data |
| 2 | Tide Data Holdings |
| 3 | Carbon Data Holdings |
| 4 | Surface Flux Data Holdings |
"Xref_Matrix_Topic" table:
| xmtid | dhmid | topicid |
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 3 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
| 6 | 4 | 1 |
| 7 | 4 | 4 |
| 8 | 4 | 5 |
The desired end-result is a view that will display each "category" in the
"DH Matrix" table and count up the number of rows in the "DH" table that
match the criteria set up in the "Xref_Matrix_Topic" table.
Matrix of Data Holdings:
| Category | # of Hits in DH |
| All Ocean Data | 4 |
| Tide Data Holdings | 2 |
| Carbon Data Holdings | 1 |
| Surface Flux Holdings | 2 |
Any help would be greatly appreciated.
TIA.What about some DDL scripts and data for us ?
Sunday, March 25, 2012
count how many documents are in the table for each name (was "Query Help")
Hello,
Brief overview. Got 2 tables, client table and document table. Both tables have client name as the primary key. Client table shows client info, address, phone, dob. Document table shows client name, document, document type.
I need to write a query that will count how many documents are in the table for each name.
This is attempt at it, please let me know whats wrong. Thanks.
SELECT count [client table].client name as cli_name, count ([document table].name as doc_qty)
FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
GROUP BY [client table].name
ORDER BY [client table].nameYour syntax is funny:
Try
count ([document table].name) as doc_qty|||wow, that was it, thanks a lot|||Agree also no count at the start.
SELECT [client table].client name as cli_name, count ([document table].name) as doc_qty
FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
GROUP BY [client table].name
ORDER BY [client table].name
Also so check the ID`s think they might be wrong or badly named.
Ie. the document table link on id not clientID
If it does not work post the tables as well|||yeah, the id thing was throwing it off too, i had to rejoin by name and client name from both tables after I gave some thought as to what exactly this query needed to do
working query:
SELECT [client table].[client name] as cli_name, count ([document table].[name]) as doc_qty
FROM [client table] INNER JOIN [document table] ON [client table].[client name] = [document table].name
GROUP BY [client table].[client name]
ORDER BY [client table].[client name];
Had another question, I'm supposed to place a constraint into both tables to lock them and not allow any new data to be entered. I have read how to write it in code, but am using access. Is there any way to modify and create tables in access2003 in sql view? I really cant stand the GUI.|||Sure, you can use a CREATE TABLE statement just as you would using Query Analyzer.|||sweet, thanks a lot for making my life a whole lot easier(for today atleast)sql
Brief overview. Got 2 tables, client table and document table. Both tables have client name as the primary key. Client table shows client info, address, phone, dob. Document table shows client name, document, document type.
I need to write a query that will count how many documents are in the table for each name.
This is attempt at it, please let me know whats wrong. Thanks.
SELECT count [client table].client name as cli_name, count ([document table].name as doc_qty)
FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
GROUP BY [client table].name
ORDER BY [client table].nameYour syntax is funny:
Try
count ([document table].name) as doc_qty|||wow, that was it, thanks a lot|||Agree also no count at the start.
SELECT [client table].client name as cli_name, count ([document table].name) as doc_qty
FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
GROUP BY [client table].name
ORDER BY [client table].name
Also so check the ID`s think they might be wrong or badly named.
Ie. the document table link on id not clientID
If it does not work post the tables as well|||yeah, the id thing was throwing it off too, i had to rejoin by name and client name from both tables after I gave some thought as to what exactly this query needed to do
working query:
SELECT [client table].[client name] as cli_name, count ([document table].[name]) as doc_qty
FROM [client table] INNER JOIN [document table] ON [client table].[client name] = [document table].name
GROUP BY [client table].[client name]
ORDER BY [client table].[client name];
Had another question, I'm supposed to place a constraint into both tables to lock them and not allow any new data to be entered. I have read how to write it in code, but am using access. Is there any way to modify and create tables in access2003 in sql view? I really cant stand the GUI.|||Sure, you can use a CREATE TABLE statement just as you would using Query Analyzer.|||sweet, thanks a lot for making my life a whole lot easier(for today atleast)sql
Subscribe to:
Posts (Atom)