Is there a better way to combine two queries

YZlat
YZlat used Ask the Experts™
on
I have a table with the following fields:

Table1
________
Date
Name
Count


I need a query that will get a count for each Name for a particular date. I also need a total count for all the names on a particular date.
I want to display it in one query so I have something like that:

    SELECT t1.Data, t1.Name, t1.Count, t2.Total FROM Table1 as t1, 
	(SELECT Date, SUM(Count) AS Total FROM Table1 WHERE Date='03/06/2017' GROUP BY Date) as t2
	WHERE t1.Date=t2.Date AND t1.Date = '04/09/2012' 

Open in new window

and
    SELECT * FROM Table1 WHERE Date = '03/06/2017' 
    UNION
    SELECT '03/06/2017', 'Total', '', SUM(Count) AS Total FROM Table1 WHERE Date = '03/06/2017'

Open in new window

I do not really like either of these solutions and wondering if there is a better alternative I have not thought of
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
any reason why you don't like it?
try something along these lines :

SELECT name, sum(count) as count where date='whatever' group by date,name with rollup

Author

Commented:
@skullnobrain, that just gives me a null row with a total for each record, I'd much rather just have a total added to each row
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
You want the same total to be repeated on every row? I would try:

SELECT t1.Data, t1.Name, t1.Count, (SELECT SUM(Count) AS Total FROM Table1 WHERE Date='03/06/2017' ) as Total 
FROM Table1 as t1
WHERE t1.Date = '04/09/2012' 

Open in new window

it might be easier to help with an example of what you expect
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
You can use the ROLLUP option from the GROUP BY:
SELECT DateColumn, COUNT(Name) NumOccurences
FROM TableName
GROUP BY ROLLUP(DateColumn)

Open in new window

NOTE: The last row with a NULL value for the DateColumn will have the grand total.

Author

Commented:
Worked for me but there are multiple rows returned for the total

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial