Sum for each category...
-
ooh I'm sick of this query and need some help here :/
I got a query that list out the following:
Country Ref# Date Description
Austria 123 20/11/2005 Some text here
Austria 234 21/11/2005 More text here
Japan 425 10/11/2005 Text text text
Japan 159 15/11/2005 Text text text
Japan 547 16/11/2005 text text text
And so on...
Now I got this question to make a result like this:
Country Ref# Date Description
Austria 123 20/11/2005 Some text here
Austria 234 21/11/2005 More text here
Sum total: 2
Japan 425 10/11/2005 Text text text
Japan 159 15/11/2005 Text text text
Japan 547 16/11/2005 text text text
Sum total: 3
My query is simple like this:
SELECT
country.country AS [Country],
orders.ref_number AS [Ref #],
orders.open_date AS [Date],
orders.Description AS [Description]
FROM
orders
left join users orders.userid = users.id
left join country users.countryid = country.id
ORDER BY
country, date
Is there a way we can do this in SQL Server 2000? We've been playing with SQL Server 2005 and it will allow us to do something like this, but we're not gonna use that for another 6 months at least
-
Try:
select country.country AS [Country],
orders.ref_number AS [Ref #],
orders.open_date AS [Date],
orders.Description AS [Description]
, COUNT(*) -- NEW
FROM orders
left join users orders.userid = users.id
left join country users.countryid = country.id
-- NEW START
GROUP BY country.country
, orders.ref_number
, orders.open_date
, orders.Description
WITH ROLLUP
-- NEW END
-- Note that with ROLLUP, an "order by" may not be included
-- ORDER BY country, date
SQL = Scarcely Qualifies as a Language
-
Thanks for that Carl, nearly there but not quite.
I was thinking about rollup but I wasn't really sure.
My problem is now that I got a lot of repeating fields with some fields as NULL values. I got each records repeated 4 times with one where all fields got data (correct!), one where Description is NULL but the other is repeated, one where date and description is NULL and one where only country and the count is displaying the total sum got data.
Like this:
Country Ref# Date Description Total
Austria 123 20/11/2005 Some text here 1
Austria 123 20/11/2005 NULL 1
Austria 123 NULL NULL 1
Austria NULL NULL NULL 1
Is there a way to avoid the NULL values?
I used:
CASE
WHEN (Grouping(orders.ref_number)=1 then 'Total records: ' + cast(count(*) as char(2))
ELSE orders.ref_number
END AS [Ref #]
and that gives me the total records correctly
so all I need now is to get rid of the rows with NULL in it
-
Yeeeahhh...i got it working
I added a having statement on the end like this:
having (
grouping(country.country) = 0
AND grouping(orders.ref_number) = 0
AND grouping(orders.open_date) = 0
AND grouping(orders.Description) = 0
)
OR
(
grouping(country.country) = 0
AND grouping(orders.ref_number) = 1
AND grouping(orders.open_date) = 1
AND grouping(orders.Description) = 1
)
That gave me only the records with data plus the sum total records for each country...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply