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 matching. Show all posts
Showing posts with label matching. 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 by matching first two letters
Say I have a table of names
How\Can I provide a count for names that have matching first two letters
For instance if my table was this
Name
DA234
DA333
ebEEE
EBddd
EEddd
I would want my output to be
DA 2
EB 2
EE 1
I hope I am communicating my question correctly and thanks for your
consideration.
ScottTry:
select
LEFT (MyCol, 2)
, count (*)
from
MyTable
group by
LEFT (MyCol, 2)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"scott" <scott@.discussions.microsoft.com> wrote in message
news:3ED42509-B63D-4376-8C81-981ED42C9D2D@.microsoft.com...
Say I have a table of names
How\Can I provide a count for names that have matching first two letters
For instance if my table was this
Name
DA234
DA333
ebEEE
EBddd
EEddd
I would want my output to be
DA 2
EB 2
EE 1
I hope I am communicating my question correctly and thanks for your
consideration.
Scott|||Try,
select left(name, 2) as l2_name, count(*) as cnt
from t1
group by left(name, 2)
AMB
"scott" wrote:
> Say I have a table of names
> How\Can I provide a count for names that have matching first two letters
> For instance if my table was this
> Name
> DA234
> DA333
> ebEEE
> EBddd
> EEddd
> I would want my output to be
> DA 2
> EB 2
> EE 1
> I hope I am communicating my question correctly and thanks for your
> consideration.
> Scott|||scott wrote:
> Say I have a table of names
> How\Can I provide a count for names that have matching first two
> letters
> For instance if my table was this
> Name
> DA234
> DA333
> ebEEE
> EBddd
> EEddd
> I would want my output to be
> DA 2
> EB 2
> EE 1
> I hope I am communicating my question correctly and thanks for your
> consideration.
> Scott
SELECT UPPER(LEFT(COL_NAME, 2)), COUNT(*)
FROM TABLE_NAME
GROUP BY UPPER(LEFT(COL_NAME, 2))
David Gugick
Imceda Software
www.imceda.com
How\Can I provide a count for names that have matching first two letters
For instance if my table was this
Name
DA234
DA333
ebEEE
EBddd
EEddd
I would want my output to be
DA 2
EB 2
EE 1
I hope I am communicating my question correctly and thanks for your
consideration.
ScottTry:
select
LEFT (MyCol, 2)
, count (*)
from
MyTable
group by
LEFT (MyCol, 2)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"scott" <scott@.discussions.microsoft.com> wrote in message
news:3ED42509-B63D-4376-8C81-981ED42C9D2D@.microsoft.com...
Say I have a table of names
How\Can I provide a count for names that have matching first two letters
For instance if my table was this
Name
DA234
DA333
ebEEE
EBddd
EEddd
I would want my output to be
DA 2
EB 2
EE 1
I hope I am communicating my question correctly and thanks for your
consideration.
Scott|||Try,
select left(name, 2) as l2_name, count(*) as cnt
from t1
group by left(name, 2)
AMB
"scott" wrote:
> Say I have a table of names
> How\Can I provide a count for names that have matching first two letters
> For instance if my table was this
> Name
> DA234
> DA333
> ebEEE
> EBddd
> EEddd
> I would want my output to be
> DA 2
> EB 2
> EE 1
> I hope I am communicating my question correctly and thanks for your
> consideration.
> Scott|||scott wrote:
> Say I have a table of names
> How\Can I provide a count for names that have matching first two
> letters
> For instance if my table was this
> Name
> DA234
> DA333
> ebEEE
> EBddd
> EEddd
> I would want my output to be
> DA 2
> EB 2
> EE 1
> I hope I am communicating my question correctly and thanks for your
> consideration.
> Scott
SELECT UPPER(LEFT(COL_NAME, 2)), COUNT(*)
FROM TABLE_NAME
GROUP BY UPPER(LEFT(COL_NAME, 2))
David Gugick
Imceda Software
www.imceda.com
Subscribe to:
Posts (Atom)