Requesting help with complicated aggregation of a table

I am moving our Databases from My SQL to SQL Server 2000 and am now starting to use views to access data. Very nice feature and worth the upgrade. I need to rewrite a report that we currently have as an Excel file that aggregates tinyint data that has p
ossible of 1-9, * (invalid) and " " (space). The way I am trying to do this in MySQL is like this:
SELECT con_id, count(q2*) (q3*) (q4*)
FROM db_data where con_id in (select con_id FROM congregs where syn_id = "1A")
GROUP BY q2, q3, q4, con_id
The following SQL Statement works with the following output but have not yet been able to get the above statement working
SELECT q3, count(*)
FROM db_data
WHERE con_id=1234567
GROUP BY q3
-- Output --
q3 count(*)
7
*1
14
238
33
I have 415 columns in the db_data table and am wondering if there is any way to write a query that does the above to the entire table (or a selection of rows "from row # to row #). To further compound the problem (and greater sweeten a solution) the poss
ible outputs change depending upon the question. So in q3, in addition to the * and blank possibilities, there can be 1, 2 or 3; in q133 there is a possible 1-7 plus the * and blank.
I can not get the above query to work in SQL Server, only MySQL.
Any help would be welcomed
Thank you
mchemnitz,
Do you mean the following?
SELECT con_id, count(q2), count(q3), count (q4)
FROM db_data where con_id in (select con_id FROM congregs where syn_id =
"1A")
GROUP BY con_id
ORDER BY con_id, q2, q3, q4
The above is COUNT(ALL col), but you might prefer COUNT (DISTINCT col).
Russell Fields
"mchemnitz" <mchemnitz@.discussions.microsoft.com> wrote in message
news:17E24782-1BC0-40C3-BCE3-EB2ED78F57D0@.microsoft.com...
> I am moving our Databases from My SQL to SQL Server 2000 and am now
starting to use views to access data. Very nice feature and worth the
upgrade. I need to rewrite a report that we currently have as an Excel file
that aggregates tinyint data that has possible of 1-9, * (invalid) and " "
(space). The way I am trying to do this in MySQL is like this:
> SELECT con_id, count(q2*) (q3*) (q4*)
> FROM db_data where con_id in (select con_id FROM congregs where syn_id =
"1A")
> GROUP BY q2, q3, q4, con_id
> The following SQL Statement works with the following output but have not
yet been able to get the above statement working
> SELECT q3, count(*)
> FROM db_data
> WHERE con_id=1234567
> GROUP BY q3
> -- Output --
> q3 count(*)
> 7
> * 1
> 1 4
> 2 38
> 3 3
> I have 415 columns in the db_data table and am wondering if there is any
way to write a query that does the above to the entire table (or a selection
of rows "from row # to row #). To further compound the problem (and greater
sweeten a solution) the possible outputs change depending upon the question.
So in q3, in addition to the * and blank possibilities, there can be 1, 2 or
3; in q133 there is a possible 1-7 plus the * and blank.
> I can not get the above query to work in SQL Server, only MySQL.
> Any help would be welcomed
> Thank you

Requesting help with complicated aggregation of a table

I am moving our Databases from My SQL to SQL Server 2000 and am now starting
to use views to access data. Very nice feature and worth the upgrade. I n
eed to rewrite a report that we currently have as an Excel file that aggrega
tes tinyint data that has p
ossible of 1-9, * (invalid) and " " (space). The way I am trying to do this
in mysql is like this:
SELECT con_id, count(q2*) (q3*) (q4*)
FROM db_data where con_id in (select con_id FROM congregs where syn_id = "1A
")
GROUP BY q2, q3, q4, con_id
The following SQL Statement works with the following output but have not yet
been able to get the above statement working
SELECT q3, count(*)
FROM db_data
WHERE con_id=1234567
GROUP BY q3
-- Output --
q3 count(*)
7
* 1
1 4
2 38
3 3
I have 415 columns in the db_data table and am wondering if there is any way
to write a query that does the above to the entire table (or a selection of
rows "from row # to row #). To further compound the problem (and greater s
weeten a solution) the poss
ible outputs change depending upon the question. So in q3, in addition to t
he * and blank possibilities, there can be 1, 2 or 3; in q133 there is a pos
sible 1-7 plus the * and blank.
I can not get the above query to work in SQL Server, only MySQL.
Any help would be welcomed
Thank youmchemnitz,
Do you mean the following?
SELECT con_id, count(q2), count(q3), count (q4)
FROM db_data where con_id in (select con_id FROM congregs where syn_id =
"1A")
GROUP BY con_id
ORDER BY con_id, q2, q3, q4
The above is COUNT(ALL col), but you might prefer COUNT (DISTINCT col).
Russell Fields
"mchemnitz" <mchemnitz@.discussions.microsoft.com> wrote in message
news:17E24782-1BC0-40C3-BCE3-EB2ED78F57D0@.microsoft.com...
> I am moving our Databases from My SQL to SQL Server 2000 and am now
starting to use views to access data. Very nice feature and worth the
upgrade. I need to rewrite a report that we currently have as an Excel file
that aggregates tinyint data that has possible of 1-9, * (invalid) and " "
(space). The way I am trying to do this in mysql is like this:
> SELECT con_id, count(q2*) (q3*) (q4*)
> FROM db_data where con_id in (select con_id FROM congregs where syn_id =
"1A")
> GROUP BY q2, q3, q4, con_id
> The following SQL Statement works with the following output but have not
yet been able to get the above statement working
> SELECT q3, count(*)
> FROM db_data
> WHERE con_id=1234567
> GROUP BY q3
> -- Output --
> q3 count(*)
> 7
> * 1
> 1 4
> 2 38
> 3 3
> I have 415 columns in the db_data table and am wondering if there is any
way to write a query that does the above to the entire table (or a selection
of rows "from row # to row #). To further compound the problem (and greater
sweeten a solution) the possible outputs change depending upon the question.
So in q3, in addition to the * and blank possibilities, there can be 1, 2 or
3; in q133 there is a possible 1-7 plus the * and blank.
> I can not get the above query to work in SQL Server, only MySQL.
> Any help would be welcomed
> Thank you

representative sub-data

Since indexed views aren't going to work for me, I'm working on
revamping a large, ugly query that hits a large, ugly database.
The database stores inventory.
Some items in inventory are single items (e.g. a calendar), while
others are grouped (e.g. a shirt with several color/size options).
The schema of the relevant tables looks like:
product:
--
ID
sku
Color
Description
Price
productGroup:
--
ID
sku
Name
productGroupElement:
--
ID
productID
productGroupID
I need a query that returns both the standalone and grouped items
together. I'm doing this with a union. The tricky part (for me) is in
getting the grouped items, grabbing representative data from one of the
standalone items that comprise the group.
Here's the current query I'm using:
select 'standalone' as flag, id, sku, color, description
from product
where id NOT IN (select productID from productGroupElement)
UNION
select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id = (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON
pge1.productID = p1.id
WHERE pge1.productGroupID =
pge.productGroupID
)
So what I'd like to know is:
1) Is the union approach a reasonably efficient way to combine
standalone and grouped items?
2) is the sub query in the WHERE clause of the second query the best
way to grab representative data?
Thanks,
ErikHI Erik
"voldengen@.gmail.com" wrote:

> Since indexed views aren't going to work for me, I'm working on
> revamping a large, ugly query that hits a large, ugly database.
> The database stores inventory.
> Some items in inventory are single items (e.g. a calendar), while
> others are grouped (e.g. a shirt with several color/size options).
> The schema of the relevant tables looks like:
> product:
> --
> ID
> sku
> Color
> Description
> Price
>
> productGroup:
> --
> ID
> sku
> Name
>
> productGroupElement:
> --
> ID
> productID
> productGroupID
>
You should check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to
post useful DDL and sample data

> I need a query that returns both the standalone and grouped items
> together. I'm doing this with a union. The tricky part (for me) is in
> getting the grouped items, grabbing representative data from one of the
> standalone items that comprise the group.
> Here's the current query I'm using:
> select 'standalone' as flag, id, sku, color, description
> from product
> where id NOT IN (select productID from productGroupElement)
> UNION
> select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
> from product p inner join productGroupElement pge on p.id =
> pge.productID
> INNER JOIN productGroup pg on pg.id = pge.productGroupID
> where p.id = (
> select max(pge1.ItemID)
> FROM productGroupElement pge1
> INNER JOIN product p1 ON
> pge1.productID = p1.id
> WHERE pge1.productGroupID =
> pge.productGroupID
> )
>
What happens to the items returned by:
select 'others' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id <> (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)

> Thanks,
> Erik
Would this give what you want?
SELECT CASE WHEN g.productID IS NULL THEN 'standalone' ELSE 'grouped' END AS
flag, p.id, p.sku, p.color, p.description
from product p
LEFT JOIN productGroupElement g ON p.id = g.productID
John|||On 29 Dec 2006 11:59:13 -0800, voldengen@.gmail.com wrote:
(snip)

>1) Is the union approach a reasonably efficient way to combine
>standalone and grouped items?
Hi Erik,
Since the constants in the SELECT lists makes the two sides of the UNION
disjunct, you should change it to UNION ALL. Without the ALL, SQL Server
will do extra work to find and remove duplicates.

>2) is the sub query in the WHERE clause of the second query the best
>way to grab representative data?
Only if a sample that is achieved by always using the highest ItemID can
be considered representative. Most statisticians would disagree. (Would
you trust a political popularity poll that is done by interviewing the
oldest member of each household?)
Hugo Kornelis, SQL Server MVP

representative sub-data

Since indexed views aren't going to work for me, I'm working on
revamping a large, ugly query that hits a large, ugly database.
The database stores inventory.
Some items in inventory are single items (e.g. a calendar), while
others are grouped (e.g. a shirt with several color/size options).
The schema of the relevant tables looks like:
product:
ID
sku
Color
Description
Price
productGroup:
ID
sku
Name
productGroupElement:
ID
productID
productGroupID
I need a query that returns both the standalone and grouped items
together. I'm doing this with a union. The tricky part (for me) is in
getting the grouped items, grabbing representative data from one of the
standalone items that comprise the group.
Here's the current query I'm using:
select 'standalone' as flag, id, sku, color, description
from product
where id NOT IN (select productID from productGroupElement)
UNION
select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id = (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON
pge1.productID = p1.id
WHERE pge1.productGroupID =
pge.productGroupID
)
So what I'd like to know is:
1) Is the union approach a reasonably efficient way to combine
standalone and grouped items?
2) is the sub query in the WHERE clause of the second query the best
way to grab representative data?
Thanks,
Erik
HI Erik
"voldengen@.gmail.com" wrote:

> Since indexed views aren't going to work for me, I'm working on
> revamping a large, ugly query that hits a large, ugly database.
> The database stores inventory.
> Some items in inventory are single items (e.g. a calendar), while
> others are grouped (e.g. a shirt with several color/size options).
> The schema of the relevant tables looks like:
> product:
> --
> ID
> sku
> Color
> Description
> Price
>
> productGroup:
> --
> ID
> sku
> Name
>
> productGroupElement:
> --
> ID
> productID
> productGroupID
>
You should check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to
post useful DDL and sample data

> I need a query that returns both the standalone and grouped items
> together. I'm doing this with a union. The tricky part (for me) is in
> getting the grouped items, grabbing representative data from one of the
> standalone items that comprise the group.
> Here's the current query I'm using:
> select 'standalone' as flag, id, sku, color, description
> from product
> where id NOT IN (select productID from productGroupElement)
> UNION
> select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
> from product p inner join productGroupElement pge on p.id =
> pge.productID
> INNER JOIN productGroup pg on pg.id = pge.productGroupID
> where p.id = (
> select max(pge1.ItemID)
> FROM productGroupElement pge1
> INNER JOIN product p1 ON
> pge1.productID = p1.id
> WHERE pge1.productGroupID =
> pge.productGroupID
> )
>
What happens to the items returned by:
select 'others' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id <> (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)

> Thanks,
> Erik
Would this give what you want?
SELECT CASE WHEN g.productID IS NULL THEN 'standalone' ELSE 'grouped' END AS
flag, p.id, p.sku, p.color, p.description
from product p
LEFT JOIN productGroupElement g ON p.id = g.productID
John

representative sub-data

Since indexed views aren't going to work for me, I'm working on
revamping a large, ugly query that hits a large, ugly database.
The database stores inventory.
Some items in inventory are single items (e.g. a calendar), while
others are grouped (e.g. a shirt with several color/size options).
The schema of the relevant tables looks like:
product:
--
ID
sku
Color
Description
Price
productGroup:
--
ID
sku
Name
productGroupElement:
--
ID
productID
productGroupID
I need a query that returns both the standalone and grouped items
together. I'm doing this with a union. The tricky part (for me) is in
getting the grouped items, grabbing representative data from one of the
standalone items that comprise the group.
Here's the current query I'm using:
select 'standalone' as flag, id, sku, color, description
from product
where id NOT IN (select productID from productGroupElement)
UNION
select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id = pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id = (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON
pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)
So what I'd like to know is:
1) Is the union approach a reasonably efficient way to combine
standalone and grouped items?
2) is the sub query in the WHERE clause of the second query the best
way to grab representative data?
Thanks,
ErikHI Erik
"voldengen@.gmail.com" wrote:
> Since indexed views aren't going to work for me, I'm working on
> revamping a large, ugly query that hits a large, ugly database.
> The database stores inventory.
> Some items in inventory are single items (e.g. a calendar), while
> others are grouped (e.g. a shirt with several color/size options).
> The schema of the relevant tables looks like:
> product:
> --
> ID
> sku
> Color
> Description
> Price
>
> productGroup:
> --
> ID
> sku
> Name
>
> productGroupElement:
> --
> ID
> productID
> productGroupID
>
You should check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to
post useful DDL and sample data
> I need a query that returns both the standalone and grouped items
> together. I'm doing this with a union. The tricky part (for me) is in
> getting the grouped items, grabbing representative data from one of the
> standalone items that comprise the group.
> Here's the current query I'm using:
> select 'standalone' as flag, id, sku, color, description
> from product
> where id NOT IN (select productID from productGroupElement)
> UNION
> select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
> from product p inner join productGroupElement pge on p.id => pge.productID
> INNER JOIN productGroup pg on pg.id = pge.productGroupID
> where p.id = (
> select max(pge1.ItemID)
> FROM productGroupElement pge1
> INNER JOIN product p1 ON
> pge1.productID = p1.id
> WHERE pge1.productGroupID => pge.productGroupID
> )
>
What happens to the items returned by:
select 'others' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id <> (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)
> Thanks,
> Erik
Would this give what you want?
SELECT CASE WHEN g.productID IS NULL THEN 'standalone' ELSE 'grouped' END AS
flag, p.id, p.sku, p.color, p.description
from product p
LEFT JOIN productGroupElement g ON p.id = g.productID
John|||On 29 Dec 2006 11:59:13 -0800, voldengen@.gmail.com wrote:
(snip)
>1) Is the union approach a reasonably efficient way to combine
>standalone and grouped items?
Hi Erik,
Since the constants in the SELECT lists makes the two sides of the UNION
disjunct, you should change it to UNION ALL. Without the ALL, SQL Server
will do extra work to find and remove duplicates.
>2) is the sub query in the WHERE clause of the second query the best
>way to grab representative data?
Only if a sample that is achieved by always using the highest ItemID can
be considered representative. Most statisticians would disagree. (Would
you trust a political popularity poll that is done by interviewing the
oldest member of each household?)
--
Hugo Kornelis, SQL Server MVP