Please modify my exisitng query

Posted on 2011-04-27
Last Modified: 2012-05-11
I have a simple table tblMain
Title                       Count
Status One           2
Status Two          3
Status Three          1
so to get the select statement :)   you go
select * from tblMain

i want to modify the query and include the grandtotal also , so the results should be :

Title                       Count
Status One           2
Status Two          3
Status Three         1
Total                     6

it is simple to use two queries and then combine using union query
I need it as one query
Question by:rfedorov
    LVL 28

    Expert Comment

    Select Title, [Status One], [Status Two], [Status Three], ([Status One] + [Status Two] + [Status Three]) As Total FROM tblMain

    Is this what you want?
    OM Gang

    Author Comment

    i did not get,  no, i do not want that
    what if i have fifty different statuses, ?
    LVL 119

    Expert Comment

    by:Rey Obrero
    try this

    select title,[count] from tblMain
    select 'Total' as title, sum([count]) from tblMain

    Author Comment

    That is what i was thinking, you use two queries and union them, I did that...
    It is working...
    is there just one select query?
    LVL 44

    Expert Comment

    You cannot get there from here.  - ie.  without using a union query ;-)
    LVL 119

    Accepted Solution

    <is there just one select query?> no, can't be done
    you have to do a union

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now