Link to home
Start Free TrialLog in
Avatar of YZlat
YZlatFlag for United States of America

asked on

Is there a better way to combine two queries

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
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

any reason why you don't like it?
ASKER CERTIFIED SOLUTION
Avatar of skullnobrains
skullnobrains

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of YZlat

ASKER

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

Avatar of skullnobrains
skullnobrains

it might be easier to help with an example of what you expect
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.
Avatar of YZlat

ASKER

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