472,325 Members | 1,768 Online

# use count & group by with union

I have a SQL query which returns the total amount of issues:

[HTML]SELECT issue,Count(issue) as total FROM mytable Group by issue[/HTML]

This works but I need the total of the same catagory in three tables so I assumed a union would work so tried something like this:

[HTML]SELECT issue,Count(issue) as total FROM mytable Group by issue
Union
SELECT issue,Count(issue) as totalFROM mytable2 Group by issue
union
SELECT issue,Count(issue) as total FROM mytable3 Group by issue[/HTML]

the problem is that the issue are duplicated so I will not get the total count I am looking for.

current results:
[HTML]
issue total
cough 5
cough 55
cough 5[/HTML]

wanted result:
[HTML]
issue total
cough 65[/HTML]

I assumed I could simply add sum(total) but did not work. What is the correct way to do this?
Jul 31 '07 #1
7 19750
hariharanmca
1,977 1GB
I have a SQL query which returns the total amount of issues:

[HTML]SELECT issue,Count(issue) as total FROM mytable Group by issue[/HTML]

This works but I need the total of the same catagory in three tables so I assumed a union would work so tried something like this:

[HTML]SELECT issue,Count(issue) as total FROM mytable Group by issue
Union
SELECT issue,Count(issue) as totalFROM mytable2 Group by issue
union
SELECT issue,Count(issue) as total FROM mytable3 Group by issue[/HTML]

the problem is that the issue are duplicated so I will not get the total count I am looking for.

current results:
[HTML]
issue total
cough 5
cough 55
cough 5[/HTML]

wanted result:
[HTML]
issue total
cough 65[/HTML]

I assumed I could simply add sum(total) but did not work. What is the correct way to do this?

You can use like

Expand|Select|Wrap|Line Numbers
1. SELECT issue,Count(issue) as total,
2. (
3. SELECT Count(issue) as total1 FROM mytable2
4. Where mytable2.issue = mytable.issue
5. ),
6. (
7. SELECT Count(issue) as total2 FROM mytable3
8. Where mytable3.issue = mytable.issue
9. )
10.  FROM mytable Group by mytable.issue
Jul 31 '07 #2
azimmer
200 Expert 100+
If my understanding of your problem is correct, you can do it the other way round:
Expand|Select|Wrap|Line Numbers
1. select issue, Count(issue) as total
2. from
3. (
4. select issue from mytable1
5. union
6. select issue from mytable2
7. union
8. select issue from mytable3
9. )
10. group by issue
11.
Jul 31 '07 #3
I will try your suggestion azimmer and post the results. I really hope that will work because the other suggestions will not give the results I wanted. with this I have four columns displayed.

issue
total (from table one only)
unnamed( count from table 2)
unnamed (count from table 3)

I need to somehow compile total + unnamed + unnamed

Thanks all
Jul 31 '07 #4
Still no luck, I got a MS SQL server syntax error (near keywork group).

Expand|Select|Wrap|Line Numbers
1. select issue, Count(issue) as total
2.
3. from
4.
5.       (
6.      select issue from mytable1
7.
8.       union
9.
10.       select issue from mytable2
11.
12.       union
13.
14.       select issue from mytable3
15.       )
16.       group by issue
Any other suggestions? I know it has to be possible.
Aug 1 '07 #5
hariharanmca
1,977 1GB
Still no luck, I got a MS SQL server syntax error (near keywork group).

Expand|Select|Wrap|Line Numbers
1. select issue, Count(issue) as total
2.
3. from
4.
5.       (
6.      select issue from mytable1
7.
8.       union
9.
10.       select issue from mytable2
11.
12.       union
13.
14.       select issue from mytable3
15.       )
16.       group by issue
Any other suggestions? I know it has to be possible.
just try Group By myTable1.issue
Aug 1 '07 #6
azimmer
200 Expert 100+
Still no luck, I got a MS SQL server syntax error (near keywork group).

Expand|Select|Wrap|Line Numbers
1. select issue, Count(issue) as total
2.
3. from
4.
5.       (
6.      select issue from mytable1
7.
8.       union
9.
10.       select issue from mytable2
11.
12.       union
13.
14.       select issue from mytable3
15.       )
16.       group by issue
Any other suggestions? I know it has to be possible.
Sorry, my fault, now I copy it from Query Analyzer (:-)):

Expand|Select|Wrap|Line Numbers
1. select x.issue, count(x.issue)
2. from (
3. select issue from mytable1
4. union all
5. select issue from mytable2
6. union all
7. select issue from mytable3
8. ) as x
9. group by x.issue
10.
Aug 1 '07 #7
Thank you. have not tried it yet but I have a good feeling about that query because I can see the logic. :-)
Aug 1 '07 #8