473,322 Members | 1,562 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 19843
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

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

Similar topics

1
by: Son KwonNam | last post by:
When I try <xsl:value-of select="count('/GROUPS/GROUP')"/> on JSTL <x:transform> tag, I got the following error message. org.apache.jasper.JasperException: Can not convert #STRING to a NodeList!...
3
by: Abhi | last post by:
Hi! I am wondering if this query is possible somehow: I have a table with many fields that all can have a value from 1 to 5. if I wanna see the count of each value from one field, then this...
2
by: Greg Strong | last post by:
Hello All, Is there a simpler way to count text boxes with data in them when the 4 text boxes are in the header of a form? I've written the code below and it works. ,----- | Private Sub...
1
by: sammy | last post by:
If you have a select with 2 attributes where you group by one attribute and do a count() for the second attribute, if the count() is 0 then that row is never displayed. How would you instead see...
0
by: RSN | last post by:
I hav a query that uses a union of 16 tables to get some counts and i need to get the end result as a Group by. This is inside a sub-query that has the count(*) ans the attribute for the group-by in...
10
by: marting | last post by:
Before I throw my new expensive laptop out of the window! I'm stuck on getting my joins right with the correct amount in a column count. After speaking with someone a few weeks back, they...
3
by: weirdguy | last post by:
Hello all, Before you read my question, I would advise you to visit the Image-Link. The image-link shows 4Tables - its field, Primary, Foreign Keys, Relationships. The bottom of the Image, it...
0
by: ryangsh | last post by:
I'm trying to consolidate inventory records in db. (I'm displaying the result in a table form but for this post I'm putting it simple) table_name= PC: department - msoffice IT - 2000 IT - 2002...
8
by: Amy Badgett | last post by:
The purpose of this query is to return seven different Counts for these 14 different EIDs (employee IDs). The problem is that only 8 of the EIDs are returned: 123, 122, 108, 164, 131, 136, 138, 150....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.