469,923 Members | 1,683 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,923 developers. It's quick & easy.

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?
Jul 31 '07 #1
7 19628
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
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

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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Abhi | last post: by
1 post views Thread by sammy | last post: by
reply views Thread by RSN | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.