Help with complex count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with complex count

Author

Topic

harmomelodic
Starting Member

4 Posts

Posted - 2009-07-02 : 17:25:45

I'm trying to set up a count across multiple tables with overlapping fields, from which I am only counting the fields that exist in all tables.
I'm expecting to start with an UNION ALL on the relevant fields

tx are tables

SELECT t1.id t1.status1 t1.statustype2
UNION ALL
SELECT t2.ids t2.statustype1 t2.status2
UNION ALL
SELECT t3.id t3.statustype1 t3.status2
UNION ALL
SELECT t4.ids t4.status1 t4.statustype2

Now comes the hard part (at least to me). I need to count each id once if and only if there is no instance of statustype2 being equal to 1 across the many repeated ids. This is not a matter of substituting a JOIN, some tables have multiple entries for a particular id on their own...only one of which might have a statustype2 = 1...which would invalidate the count for all instances of that id.

If there exists a entry in any table for id x, count that id once, if and only if the field statustype1 != 1 for any instance of id = x.

Now how the heck do I express that in SQL????

Additionally, I'm keying the count by statustype1 (so GROUP BY statustype1).

I feel like I have some, not all, of the pieces, but I don't know how to combine them properly and I can't figure out the 'avoid statustype2 = 1' part of the query.

Your help is GREATLY appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-02 : 17:35:40

select id
FROM (
SELECT t1.id, t1.status1, t1.statustype1 from t1
UNION ALL
SELECT t2.id, t2.status2, t2.statustype2 from t2
UNION ALL
SELECT t3.id, t3.status3, t3.statustype3 from t3
UNION ALL
SELECT t4.id, t4.status4, t4.statustype4 from t4
) AS d
group by id
having max(case when statustype1 = 1 THEN 1 ELSE 0 END) = 0

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

harmomelodic
Starting Member

4 Posts

Posted - 2009-07-02 : 18:21:05

Brilliant, that brings me significantly closer!

A couple questions/caveats

1) the table fields, while containing related information, have differnet names. Does it key on the names according to t1, or do I need to do something to make that work?

2) Unless I'm mistaken, this will tell me, for each id, wheather any statustype2 is a 1...
e.g.

i.d. d
---------------
0001 0
0002 1 (one or more 0002 entries contains a 1 in statustype2)
0003 0
0004 0

etc.

What I am looking for is....

statustype1 total ids without a 1 in any statustype2 for an id
--------------------------------------------------------------------
type1 152
type2 310
type3 111

etc.

Am I mistaken?
Don't I need a COUNT somewhere?
How do I use a COUNT in this context (unless I really don't need one).

Thanks again...well on the way to figuring this out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-02 : 18:38:15

select id , count(*)
FROM (
SELECT t1.id, t1.status1, t1.statustype1 from t1
UNION ALL
SELECT t2.id, t2.status2, t2.statustype2 from t2
UNION ALL
SELECT t3.id, t3.status3, t3.statustype3 from t3
UNION ALL
SELECT t4.id, t4.status4, t4.statustype4 from t4
) AS d
group by id
having max(case when statustype1 = 1 THEN 1 ELSE 0 END) = 0

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

harmomelodic
Starting Member

4 Posts

Posted - 2009-07-03 : 01:49:18

Sigh,
Access database can't deal with the case statement.
I have substituted IIf(statustype1 = '1', 1, 0) so that the query reads...

SELECT statustype1, COUNT(ID) AS subtotal
FROM (
SELECT ID, statustype1, statustype2 FROM t1
UNION ALL
SELECT ID, statustype1, statustype2x FROM t2
) AS d
GROUP BY statustype1
HAVING 0 = MAX(IIf([statustype2 = '1', 1, 0))

It works if I remove the HAVING clause. Of course, that is the key to this entire query...
with the clause it returns only a few...and inspection shows that it should return far more. Based on what it does return (a few malformed records) I'm wondering if it is doing the match incorretly, or only working when there is only a single bad record?
Regardless, thoughts would be appreciated!
I also think I'm missing a DISTINCT clause in there, but I want to get the HAVING clause working before I move on...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-03 : 02:30:42

I don't think you can do COUNT(DISTINCT ID) in Access, can you?

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-03 : 02:49:41

Yes. The only way is

select count(id) from
(
select distinct id from table
) as t

Madhivanan

Failing to plan is Planning to fail

harmomelodic
Starting Member

4 Posts

Posted - 2009-07-05 : 13:28:33

The following mostly worked.....the subquery UNION was required to be in a separate query by Access' limitations. Same with the COUNT().
The part I am still struggling with...the DISTINCT clause allows for multiple counts when there are multiple statustype1's that exist for a single ID. I still need a way to either force it to pick 1 (arbitrary choice) or for it to split the count.
Alternatively, I could construct an UPDATE statement to fix the source so that there are no cases of the above problem. Most of the redundancies are when some entries are NULL due to data entry issues (the status in this case is not time dependant, which is the reason I can not do the same to fix the statustype2 field).

SELECT statustype1, COUNT(ID) AS subtotal
FROM
(
SELECT DISTINCT ID, statustype1 FROM
(
SELECT ID, statustype1, statustype2 FROM t1
UNION ALL
SELECT ID, statustype1, statustype2x FROM t2
) AS d
WHERE ID NOT IN
(SELECT ID FROM ID_1)
) AS e
GROUP BY statustype1

query ID_1...

SELECT ID FROM t1 WHERE statustype2 = '1'
UNION ALL
SELECT ID FROM t2 WHERE statustype2 = '1';

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources