use count & group by with union

[sharijl]

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
headache 4
headache 30
headache 2
cough 5
cough 55
cough 5[/HTML]

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


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

[hariharanmca]

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
headache 4
headache 30
headache 2
cough 5
cough 55
cough 5[/HTML]

wanted result:
[HTML]
issue total
headache 36
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

SELECT issue,Count(issue) as total, 
(
SELECT Count(issue) as total1 FROM mytable2 
Where mytable2.issue = mytable.issue
),
(
SELECT Count(issue) as total2 FROM mytable3
Where mytable3.issue = mytable.issue
)
 FROM mytable Group by mytable.issue

[azimmer]

If my understanding of your problem is correct, you can do it the other way round:

select issue, Count(issue) as total
from
(
select issue from mytable1
union
select issue from mytable2
union
select issue from mytable3
)
group by issue
 

[sharijl]

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

[sharijl]

Still no luck, I got a MS SQL server syntax error (near keywork group).

select issue, Count(issue) as total
 
from
 
      (
     select issue from mytable1
 
      union
 
      select issue from mytable2
 
      union
 
      select issue from mytable3
      )
      group by issue

Any other suggestions? I know it has to be possible.

[hariharanmca]

Still no luck, I got a MS SQL server syntax error (near keywork group).

select issue, Count(issue) as total
 
from
 
      (
     select issue from mytable1
 
      union
 
      select issue from mytable2
 
      union
 
      select issue from mytable3
      )
      group by issue

Any other suggestions? I know it has to be possible.

just try Group By myTable1.issue

[azimmer]

Still no luck, I got a MS SQL server syntax error (near keywork group).

select issue, Count(issue) as total
 
from
 
      (
     select issue from mytable1
 
      union
 
      select issue from mytable2
 
      union
 
      select issue from mytable3
      )
      group by issue

Any other suggestions? I know it has to be possible.

Sorry, my fault, now I copy it from Query Analyzer (:-)):

select x.issue, count(x.issue)
from (
select issue from mytable1
union all
select issue from mytable2
union all
select issue from mytable3
) as x
group by x.issue
 

[sharijl]

Thank you. have not tried it yet but I have a good feeling about that query because I can see the logic. :-)