Avatar of YZlat
YZlat
Flag 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
Microsoft SQL Server

Avatar of undefined
Last Comment
YZlat

8/22/2022 - Mon
Éric Moreau

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Éric Moreau

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
skullnobrains

it might be easier to help with an example of what you expect
Vitor Montalvão

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.
YZlat

ASKER
Worked for me but there are multiple rows returned for the total
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.