Firehose mode: write

Showing posts with label write. Show all posts

Friday, March 23, 2012

Grouping

I'm trying to write a report and need to group columns that are alike.
For example.

DeptID JobTitle

40344 Sales Clerk
1st Assistant
Store Manager

40666 Sales Clerk
2nd Assistant
Store Manager

Sorry, it will not output correctly, I hope you get what I'm trying to do.
I can get the results above but it puts the deptid for each job title. I do not want it. Any help will be appreciated. Thanks in advance.Why? This is just a display issue. Can't you handle it in the frontend?|||Not if its just a query. I was thinking about doing the whole cursor deal and looping through that to give me my desired results but i thought it would be an easier way.|||You could do it as two selects. Select into a table variable with an identity column. Then select from the table variable and show blank if it's not the max(identity) for the given DeptID. Make sense?|||Yes, thank you.|||What about appending a carriage return to the last field: + char(13)?|||What about appending a carriage return to the last field: + char(13)?If you pursue that route, you need to "crelf it"... Instead of just a carriage return, you need a carriage return followed by a line feed which is: + Char(13) + Char(10)

-PatP

Wednesday, March 21, 2012

grouped by month

Dear All,

I'd like to write a query that lists items from a single table but groups the listed items by a date (data of item entered into the table)

So all items matching a criteria and were entered during March should be listed underneath one-another
Then all items matching the same criteria but entered during April should be grouped again.

Not sure what would be the right approach here.

I'm thinkning, creating a temp table putting data in there but altering the data enterd field into just year and month, and then group the result by that field?

Will this work?group by month(Date)

More over
group by year(date), month(date)

Group Update of Prices

I am trying to write a SP that will update the price of products that are in a particular category.

I have 3 tables;
PRODUCT
- productID, price

CATEGORY
- categoryID, categoryName, parentID

PRODUCT_CATEGORY_MAP
- productID, categoryID

What I want to do, is allow admin to pass is a new price and categoryID, and it will update all the products that are within the supplied category.

I will start with:
UPDATE PRODUCT SET price = @.price WHERE categoryID = @.categoryID
however I do not have categoryID within the PRODUCT table

Do I place a SELECT statement after the WHERE?

DECLARE @.productID int
UPDATE PRODUCT SET price = @.price WHERE (SELECT @.productID = productID FROM PRODUCT_CATEGORY_MAP WHERE categoryID=@.categoryID)

Not sure if that would work or not or if I am on the right track.
Thanks for any help,
Mick

You just join in the other tables like this (given that you only need categoryID and that's in the map table, there is no need to join Category too, although you could if you were filtering on Category name for example)

UPDATE Product SET price = @.price
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
WHERE PCM.categoryID = @.categoryID

|||

Ok, I ended up figuring it out, but using IN instead of INNER JOIN. I like yours better.

So I want to extend this a little more...
Say I have the categories
ID Name ParentID
1 Cat1 0
2 Cat2 1

I have assigned a product to Cat2
productID categoryID
1 2

I now want my SP to work so that if a user selects the category Cat1, it will also update all child products.
I have a SP that gets all the child categories...
SELECT categoryID FROM Category
WHERE categoryID IN (SELECT id FROM dbo.GetChildren(@.categoryID))

I have this so far, but it only updates categories in the selected category, not it's childs as well.
UPDATE Product SET ourUSDPrice = @.newPrice
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
WHERE PCM.categoryID IN
(SELECT categoryID FROM Category C
WHERE C.categoryID IN (SELECT id FROM dbo.GetChildren(@.categoryID))
AND PCM.categoryID=@.categoryID)

Thanks again,
Mick

|||

UPDATE Product SET ourUSDPrice = @.newPrice
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
INNER JOIN dbo.GetChildren(@.categoryID)) as ChildCategory on PCM.categoryID = ChildCategory.Id

--or

UPDATE Product SET ourUSDPrice = @.newPrice
Where Exists (Select ProductId From Product P INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID INNER JOIN dbo.GetChildren(@.categoryID)) as ChildCategory on PCM.categoryID = ChildCategory.Id)

|||Mani has posted two ways to make your query work - do you have the GetChildren function working, so that it gets all the children at all levels? I wasn't sure from your post whether you were asking about that or not.

Monday, March 12, 2012

GROUP BY with WHERE?

Hi, I'm trying to write a query to give the following output:
Qualification Registered Passed Failed W/Drawn
MATH 25 15 10 0
OR
MATH Reg 25
MATH Pass 10
MATH Failed 10
I figure I need to select sum(RegistrationID) and group by
Qualification & Outcome but the problem is the number of registered is
the sum of all outcomes, and passed/failed/withdrawn are just for 1
outcome.
Is there any way to get this output? DDL Below.
The Qual names and Outcomes are just numbers in this example.
Thanks
CREATE TABLE [dbo].[_Courses] (
[CourseID] [smallint] NOT NULL ,
[Qualification] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[_Registrations] (
[RegistrationID] [smallint] NOT NULL ,
[CourseID] [smallint] NOT NULL ,
[CandidateID] [smallint] NOT NULL ,
[Outcome] [tinyint] NOT NULL
) ON [PRIMARY]
GOOops... Additional DDL
ALTER TABLE [dbo].[_Courses] WITH NOCHECK ADD
CONSTRAINT [PK__Courses] PRIMARY KEY CLUSTERED
(
[CourseID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[_Registrations] WITH NOCHECK ADD
CONSTRAINT [PK_Registrations] PRIMARY KEY CLUSTERED
(
[RegistrationID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[_Registrations] ADD
CONSTRAINT [FK__Registrations__Courses] FOREIGN KEY
(
[CourseID]
) REFERENCES [dbo].[_Courses] (
[CourseID]
)
GO|||What is the relation of Passed, Failed, W/Drawn to Outcome?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1127242417.999335.168990@.g14g2000cwa.googlegroups.com...
Hi, I'm trying to write a query to give the following output:
Qualification Registered Passed Failed W/Drawn
MATH 25 15 10 0
OR
MATH Reg 25
MATH Pass 10
MATH Failed 10
I figure I need to select sum(RegistrationID) and group by
Qualification & Outcome but the problem is the number of registered is
the sum of all outcomes, and passed/failed/withdrawn are just for 1
outcome.
Is there any way to get this output? DDL Below.
The Qual names and Outcomes are just numbers in this example.
Thanks
CREATE TABLE [dbo].[_Courses] (
[CourseID] [smallint] NOT NULL ,
[Qualification] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[_Registrations] (
[RegistrationID] [smallint] NOT NULL ,
[CourseID] [smallint] NOT NULL ,
[CandidateID] [smallint] NOT NULL ,
[Outcome] [tinyint] NOT NULL
) ON [PRIMARY]
GO|||There is just another lookup table _Outcome that Links to Outcome on
Registrations. It just holds the names of each outcome 1=Registered,
2=Pass, 3=Fail,4=Withdrawn etc.|||Nothing but guesswork:
SELECT c1.Qualification,
SUM( CASE r1.Outcome WHEN 1 THEN 1 ELSE 0 END ) AS "Registered",
SUM( CASE r1.Outcome WHEN 2 THEN 1 ELSE 0 END ) AS "Pass",
SUM( CASE r1.Outcome WHEN 3 THEN 1 ELSE 0 END ) AS "Fail",
SUM( CASE r1.Outcome WHEN 4 THEN 1 ELSE 0 END ) AS "Withdrawn"
FROM Courses c1
INNER JOIN Registrations r1
ON c1.CourseID = r1.CourseID
GROUP BY c1.Qualification ;
Anith|||Here's an example without pivoting:
select
c.Qualification
, o.OutcomeDescription
, count (r.RegistrationID) as Registrations
from
_Courses c
cross
join
_Outcomes o
left
join
_Registrations r on r.CourseID = c.CourseID
and r.Outcome = o.Outcome
group by
c.Qualification
, o.OutcomeDescription
If you have sample data, we could test better.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1127245679.021677.199090@.g47g2000cwa.googlegroups.com...
There is just another lookup table _Outcome that Links to Outcome on
Registrations. It just holds the names of each outcome 1=Registered,
2=Pass, 3=Fail,4=Withdrawn etc.|||Thanks - both of these work.

Friday, March 9, 2012

GROUP BY problem

Hi, I'm trying to write a query that gives me the total number of documents that exist in a table and also the total number of
documents that were updated within the last 30 days. I need the new_documents field to contain 0 if no new documents were found. Here's what I have so far:

SELECT b.doc_type,
si_service_code_lookup.code_name,
COUNT(b.doc_type) total_documents,
COUN(b.doc_type) new_documents
FROM (SELECT DISTINCT a.doc_type
FROM (SELECT documents_by_esn_vu.doc_type
FROM documents_by_esn_vu
WHERE documents_by_esn_vu.doc_orig_date
BETWEEN SYSDATE AND (SYSDATE - 30)) a ORDER BY a.doc-type) b,
si_service_code_lookup
WHERE b.doc_type = si_service_code_lookup.code
AND si_service_code_lookup.code_type = 'Parts'
GROUP BY b.doc_type,
si_service_code_lookup.code_name;

Any ideas on this?Try this:

SELECT d.doc_type,
l.code_name,
COUNT(*) total_documents,
SUM(CASE WHEN d.doc_orig_date BETWEEN SYSDATE-30 AND SYSDATE THEN 1 ELSE 0 END) new_documents
FROM si_service_code_lookup l,
documents_by_esn_vu d
WHERE d.doc_type = l.code
AND l.code_type = 'Parts'
GROUP BY d.doc_type,
l.code_name;

Or if CASE doesn't work for your version of Oracle:

SELECT d.doc_type,
l.code_name,
COUNT(*) total_documents,
SUM(DECODE(SIGN(d.doc_orig_date-(SYSDATE-30)),1,1,0)) new_documents
FROM si_service_code_lookup l,
documents_by_esn_vu d
WHERE d.doc_type = l.code
AND l.code_type = 'Parts'
GROUP BY d.doc_type,
l.code_name;|||Thanks for the help. That's exactly what I needed.

Wednesday, March 7, 2012

group by issue

Alright, so I'm trying to bind a list of pictures to a repeater but the sql I'm trying to write is killing me. The goal is simple: Write a query that returns all the pictures where the selected people are tagged but only return those pictures where all selected people are in them. So far I have the following SQL statement.

SELECT picture.id, picture.name, picture.album_id,
tag.id , tag.picture_id, tag.people_id
FROM picture LEFT OUTER JOIN
tag ON picture.id = tag.picture_id
WHERE (tag.people_id = '1') OR
(tag.people_id = '3')
GROUP BY picture.id
HAVING (COUNT(picture.id) > 1)

Now the problem is that you have to include all the columns that appear in the SELECT statement in the GROUP BY clause otherwise it errors out (this query will do that) but if I include them all then I get zero results when I know I should have 1 result given this particular query's desired result. Now when I was working with mySQL you could leave out columns in the GROUP BY and mySQL would handle it but seems SQL Server is not so forgiving.

Any ideas?

Without seeing the data, etc, it's hard to say for sure, but I suspect that the group by is not your problem (and btw, a group by without including the group by fields in the select list makes absolutely no sense, even though I know some databases may implicitely add the columsn for you -- not sure how mySQL handles it). I suspect you may be running into a subtle problem with the new ansi syntax for outer joins (maybe I'm wrong) when you include a test on the outer table. Seehttp://www.databasejournal.com/features/mssql/article.php/1438001 and scroll down to "outer join gotchas"

|||

I think I have the outer join right...see below

Produces (just test data):

nameidfilenameCopy of Copy of Copy of IMG_0052.jpg90~\Pictures\Copy of Copy of Copy of IMG_0052.jpgCopy of Copy of Copy of IMG_0052.jpg90~\Pictures\Copy of Copy of Copy of IMG_0052.jpgCopy of Copy of Copy of IMG_0053.jpg91~\Pictures\Copy of Copy of Copy of IMG_0053.jpgCopy of Copy of Copy of IMG_0054.jpg92~\Pictures\Copy of Copy of Copy of IMG_0054.jpg

Now this produces an output of 4 results because person 1 & 3 are both tagged in two pictures with one of those pictures containing both people. Now what I need to do is tweak the query so that it only returns that one record, the one that is duplicated. And now that I've typed this all out something has come to me so I tried the following query.

SELECT picture.name, picture.id, folders.pictures + picture.name AS filename
FROM folders CROSS JOIN
picture LEFT OUTER JOIN
tag ON picture.id = tag.picture_id
WHERE (tag.people_id = '1') OR
(tag.people_id = '3')
GROUP BY picture.name, picture.id, folders.pictures, picture.name
HAVING (COUNT(picture.id) > 1)

And that did it. Finally got the number that I was looking for. And the below query produces the count on the previous page to notify the user how many results the above query will return when they click search

SELECT COUNT(DISTINCT picture.name) FROM picture left OUTER JOIN tag ON picture.id = tag.picture_id where ( people_id = '1' or people_id = '3' ) GROUP BY picture.name HAVING (COUNT(picture.name) > 1)

Thanks for making me think a little more into it. Sometimes you get so stuck in one way of thinking it takes something else to get you to think about it from a different perspective, plus the fact I always have trouble with group by clauses

Group by hour, month etc

Hi All,

I have an orders table which has a filed called OrderTime which is the exact time when the order was received ? I need to write the following queries

1. Get number of orders per year

2. Get number of orders per month (obviously if a month (for example, november) is in a different year, its to be in a different row)

3. Get number of orders per hour

Note: If there are no orders in a year etc, it should return a row with 0.

You need to use the DatePart function in your SQL.|||

select datepart(year, OrderTime), count(*) from table group by datepart(year, OrderTime)

select year(OrderTime), count(*) from table group by year(OrderTime)

select dateadd(year, datediff(year, 0, OrderTime), 0), count(*) fromtable group by dateadd(month, datediff(month, 0, OrderTime), 0)

select dateadd(month, datediff(month, 0, OrderTime), 0), count(*) from table group by dateadd(month, datediff(month, 0, OrderTime), 0)

select dateadd(hour, datediff(hour, 0, OrderTime), 0), count(*) from table group by dateadd(hour, datediff(hour, 0, OrderTime), 0)

|||

sachinsurana:
Note: If there are no orders in a year etc, it should return a row with 0.

No order for which year ? Year 2008, 2009, 3000 ? You have to define what are the range of year. If you have a calendar table, use it to LEFT JOIN to your table.

|||I dont have a calender table and I can't create one as we are not supposed to modify the database.|||

then create one on the fly

select [year]

from

(

select 2000 as [year] union all select 2001 as [year] union all select 2002 as [year]

) y

make use of this function http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

Sunday, February 26, 2012

Group By clause with an inserted column

Good afternoon all,
I'm trying to write SQL that adds a descriptive column and groups on that
column but I get an error saying my inserted column is invalid. Can anyone
help? An example follows.
Sales Table:
Sales Type Amount
A 5.00
A 6.00
B 2.00
SQL:
SELECT
(CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE SALES'
END), SUM(Amount)
GROUP BY '?
Desired Result:
TAXABLE SALES 11.00
NONTAXABLE SALES 2.00
Thanks in advance,
Don J> GROUP BY '?
CASE
WHEN Sales Type = 'A' THEN 'TAXABLE SALES'
ELSE 'NONTAXABLE SALES'
END
AMB
"Don Jellie" wrote:

> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on that
> column but I get an error saying my inserted column is invalid. Can anyon
e
> help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE SALES
'
> END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J|||Don Jellie wrote:
> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on
> that column but I get an error saying my inserted column is invalid.
> Can anyone help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE
> SALES' END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J
create table #a (SalesType char(1) NOT NULL, Amount DECIMAL(10, 2) NOT
NULL)
go
Insert Into #a Values ('A', 5.00)
Insert Into #a Values ('A', 6.00)
Insert Into #a Values ('B', 2.00)
go
SELECT
CASE
WHEN SalesType = 'A' THEN 'TAXABLE SALES'
ELSE 'NONTAXABLE SALES'
END,
SUM(Amount)
From
#a
GROUP BY SalesType
go
drop table #a
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Since your column named Sales Type has a space in it, you must "Quote" it by
wrapping it in " " or [ ] like so:
SELECT
CASE WHEN [Sales Type] = 'A'
THEN 'Taxable Sales'
ELSE 'NonTaxable Sales'
END AS "Sales Type",
SUM(Amount) AS "Amount"
GROUP BY [Sales Type]
"Don Jellie" <Jelliebean1@.msn.nospam.com> wrote in message
news:ADDCEAC9-659D-4EA2-8252-25E5DB0D6840@.microsoft.com...
> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on that
> column but I get an error saying my inserted column is invalid. Can
anyone
> help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE
SALES'
> END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J

Friday, February 24, 2012

group by

id name address

1 rocket xxxx
2 scooter
3 bus 5555
2 bike 8888
3 bus 999
4 road 6666
4 7777
1 canal yyyy

I want to write a query to get the result from the above data as shown below

id name address
1 rocket xxxx
2 scooter 8888
3 bus 5555
4 road 6666

i should get the first non-nul value of name and address group by id.Assuming you name your table as tblData, the following will work:

SELECT DISTINCT D.[id],
(SELECT TOP 1 name FROM tblData WHERE [id] = D.[id] AND name IS NOT NULL) AS name,
(SELECT TOP 1 address FROM tblData WHERE [id] = D.[id] AND address IS NOT NULL) AS address
FROM tblData D
ORDER BY D.[id]

It may not be the prettiest solution though...|||SELECT id, MIN(name) name, MIN(address)
FROM table
GROUP BY id
ORDER BY id|||"first non-nul value of name and address group by id"?

There is no "first" value for an ID unless you specify additional sort orders that define a unique composite key. If you just want the first value alphabetically, RogerWilco's simple solution will work fine. If you have in mind some different sort logic, or you are relying upon the order in which the data is stored in the table, then you need to rethink your design.

Group By

I am try to write a query that would group my data
this is what I have
SELECT [CORP-NUM],
[GL-ACCT-NUM-II-PRIN],
[GL-ACCT-NUM-II-IE],
[CURRENT-BOOK-PRINCIPAL],
sum (dbo.current_month_09_2005.BookAmount)as [Book Amount],
(dbo.Copy_AFS.[CURRENT-BOOK-PRINCIPAL]-
dbo.current_month_09_2005.BookAmount)as Diff,
SUBSYSID
FROM dbo.Copy_AFS
LEFT JOIN dbo.current_month_09_2005
ON dbo.Copy_AFS.Key_tdm = dbo.current_month_09_2005.Key_Tdm
GROUP BY[CORP-NUM]
Howver I am getting the following error
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.GL-ACCT-NUM-II-PRIN' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.GL-ACCT-NUM-II-IE' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.CURRENT-BOOK-PRINCIPAL' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.CURRENT-BOOK-PRINCIPAL' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.current_month_09_2005.BookAmount' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.SUBSYSID' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
Can some one tell me how to fix this
Thanks
ChrisAll non-aggregate columns in your SELECT list must appear in the GROUP BY.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A980D39B-E799-4267-9318-CE76A967DD7C@.microsoft.com...
I am try to write a query that would group my data
this is what I have
SELECT [CORP-NUM],
[GL-ACCT-NUM-II-PRIN],
[GL-ACCT-NUM-II-IE],
[CURRENT-BOOK-PRINCIPAL],
sum (dbo.current_month_09_2005.BookAmount)as [Book Amount],
(dbo.Copy_AFS.[CURRENT-BOOK-PRINCIPAL]-
dbo.current_month_09_2005.BookAmount)as Diff,
SUBSYSID
FROM dbo.Copy_AFS
LEFT JOIN dbo.current_month_09_2005
ON dbo.Copy_AFS.Key_tdm = dbo.current_month_09_2005.Key_Tdm
GROUP BY[CORP-NUM]
Howver I am getting the following error
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.GL-ACCT-NUM-II-PRIN' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.GL-ACCT-NUM-II-IE' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.CURRENT-BOOK-PRINCIPAL' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.CURRENT-BOOK-PRINCIPAL' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.current_month_09_2005.BookAmount' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.SUBSYSID' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
Can some one tell me how to fix this
Thanks
Chris|||Each column listed in the SELECT clause must be listed in the GROUP BY claus
e
(except - of course - the aggregate):
select <column list>
,<aggregate>
from <table>
group by <column list>
What exactly is your goal?
ML
http://milambda.blogspot.com/|||Chris (Chris@.discussions.microsoft.com) writes:
> I am try to write a query that would group my data
> this is what I have
> SELECT [CORP-NUM],
> [GL-ACCT-NUM-II-PRIN],
> [GL-ACCT-NUM-II-IE],
> [CURRENT-BOOK-PRINCIPAL],
> sum (dbo.current_month_09_2005.BookAmount)as [Book Amount],
> (dbo.Copy_AFS.[CURRENT-BOOK-PRINCIPAL]-
> dbo.current_month_09_2005.BookAmount)as Diff,
> SUBSYSID
> FROM dbo.Copy_AFS
> LEFT JOIN dbo.current_month_09_2005
> ON dbo.Copy_AFS.Key_tdm = dbo.current_month_09_2005.Key_Tdm
> GROUP BY[CORP-NUM]
> Howver I am getting the following error
> Server: Msg 8120, Level 16, State 1, Line 5
> Column 'dbo.Copy_AFS.GL-ACCT-NUM-II-PRIN' is invalid in the select list
> because it is not contained in either an aggregate function or the GROUP
> BY clause.
>...
> Can some one tell me how to fix this
No, becase I don't know what outupt you are really looking for. The error
would away ir you add all non-aggregated columns (that is, those outside
the SUM()) to the GROUP BY clause. But whether that will give you the
result you are looking for, I have no idea.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Sunday, February 19, 2012

groug query

I m having 2 details table as purdtl and saledtl
i wanted to write a query by joining both details tables
purdtl table is having fields like itemcode,
batchno,qty,purno,rate,vatrate,vatamt
saledtl is having fields like itemcode, batchno,qty,saleno,irqty
i want to write a query having
purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
from both the tables
plese help me for writing above query
--
Yousuf Khan
ProgrammerHi
SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
purdtl.itemcode=saledtl.itemcode
Take a look at GROUP BY clause in the BOL
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>I m having 2 details table as purdtl and saledtl
> i wanted to write a query by joining both details tables
> purdtl table is having fields like itemcode,
> batchno,qty,purno,rate,vatrate,vatamt
> saledtl is having fields like itemcode, batchno,qty,saleno,irqty
> i want to write a query having
> purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
> saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
> from both the tables
> plese help me for writing above query
>
>
>
> --
> Yousuf Khan
> Programmer|||Can you please give me example using my tables i am not able to group it coz
both table are detail tables
]
--
Yousuf Khan
Programmer
"Uri Dimant" wrote:
> Hi
> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
> purdtl.itemcode=saledtl.itemcode
> Take a look at GROUP BY clause in the BOL
>
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
> >I m having 2 details table as purdtl and saledtl
> > i wanted to write a query by joining both details tables
> >
> > purdtl table is having fields like itemcode,
> > batchno,qty,purno,rate,vatrate,vatamt
> >
> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
> >
> > i want to write a query having
> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
> > from both the tables
> > plese help me for writing above query
> >
> >
> >
> >
> >
> >
> >
> > --
> > Yousuf Khan
> > Programmer
>
>|||> Can you please give me example using my tables i am not able to group it
> coz
> both table are detail tables
Yes , if you provide DDL+ sample data + expected result
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
> Can you please give me example using my tables i am not able to group it
> coz
> both table are detail tables
> ]
> --
> Yousuf Khan
> Programmer
>
> "Uri Dimant" wrote:
>> Hi
>> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
>> purdtl.itemcode=saledtl.itemcode
>> Take a look at GROUP BY clause in the BOL
>>
>>
>>
>> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
>> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>> >I m having 2 details table as purdtl and saledtl
>> > i wanted to write a query by joining both details tables
>> >
>> > purdtl table is having fields like itemcode,
>> > batchno,qty,purno,rate,vatrate,vatamt
>> >
>> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
>> >
>> > i want to write a query having
>> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
>> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
>> > from both the tables
>> > plese help me for writing above query
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > --
>> > Yousuf Khan
>> > Programmer
>>|||i have already told in my first question how i wanted the result and
i have given the fields also
--
Yousuf Khan
Programmer
"Uri Dimant" wrote:
> > Can you please give me example using my tables i am not able to group it
> > coz
> > both table are detail tables
> Yes , if you provide DDL+ sample data + expected result
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
> > Can you please give me example using my tables i am not able to group it
> > coz
> > both table are detail tables
> >
> > ]
> > --
> > Yousuf Khan
> > Programmer
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Hi
> >> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
> >> purdtl.itemcode=saledtl.itemcode
> >>
> >> Take a look at GROUP BY clause in the BOL
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> >> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
> >> >I m having 2 details table as purdtl and saledtl
> >> > i wanted to write a query by joining both details tables
> >> >
> >> > purdtl table is having fields like itemcode,
> >> > batchno,qty,purno,rate,vatrate,vatamt
> >> >
> >> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
> >> >
> >> > i want to write a query having
> >> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
> >> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
> >> > from both the tables
> >> > plese help me for writing above query
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > --
> >> > Yousuf Khan
> >> > Programmer
> >>
> >>
> >>
>
>|||Its not enough info.
On what column you want to join? If you cant join tables directly, what is
the relationship? You also didnt post sample data, post a couple of rows in
both tables and then tell us what the result should be...
MC
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:BB081002-5B93-4BFD-BC10-15622CDE71B2@.microsoft.com...
>i have already told in my first question how i wanted the result and
> i have given the fields also
> --
> Yousuf Khan
> Programmer
>
> "Uri Dimant" wrote:
>> > Can you please give me example using my tables i am not able to group
>> > it
>> > coz
>> > both table are detail tables
>> Yes , if you provide DDL+ sample data + expected result
>>
>>
>> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
>> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
>> > Can you please give me example using my tables i am not able to group
>> > it
>> > coz
>> > both table are detail tables
>> >
>> > ]
>> > --
>> > Yousuf Khan
>> > Programmer
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Hi
>> >> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
>> >> purdtl.itemcode=saledtl.itemcode
>> >>
>> >> Take a look at GROUP BY clause in the BOL
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
>> >> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>> >> >I m having 2 details table as purdtl and saledtl
>> >> > i wanted to write a query by joining both details tables
>> >> >
>> >> > purdtl table is having fields like itemcode,
>> >> > batchno,qty,purno,rate,vatrate,vatamt
>> >> >
>> >> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
>> >> >
>> >> > i want to write a query having
>> >> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
>> >> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
>> >> > from both the tables
>> >> > plese help me for writing above query
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Yousuf Khan
>> >> > Programmer
>> >>
>> >>
>> >>
>>|||Hi
look at this
Select purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
from
purdtl,saledtl
where purdtl.itemcode = saledtl.itemcode
and purdtl.batchno = saledtl.batchno
group by
purdtl.itemcode,purdtl.batchno,saledtl.itemcode,saledtl.batchno|||And you try so hard:( :)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O5a0ykFZGHA.4836@.TK2MSFTNGP05.phx.gbl...
>> Can you please give me example using my tables i am not able to group it
>> coz
>> both table are detail tables
> Yes , if you provide DDL+ sample data + expected result
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
>> Can you please give me example using my tables i am not able to group it
>> coz
>> both table are detail tables
>> ]
>> --
>> Yousuf Khan
>> Programmer
>>
>> "Uri Dimant" wrote:
>> Hi
>> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
>> purdtl.itemcode=saledtl.itemcode
>> Take a look at GROUP BY clause in the BOL
>>
>>
>>
>> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
>> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>> >I m having 2 details table as purdtl and saledtl
>> > i wanted to write a query by joining both details tables
>> >
>> > purdtl table is having fields like itemcode,
>> > batchno,qty,purno,rate,vatrate,vatamt
>> >
>> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
>> >
>> > i want to write a query having
>> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
>> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
>> > from both the tables
>> > plese help me for writing above query
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > --
>> > Yousuf Khan
>> > Programmer
>>
>|||both table are having same type of data
itemcode, batchno, qty, rate and their can be repeatation of same itemcode
and batchno
table purdtl is detail table of items purchase and table saledtl is detail
table of sale items
i want the result the total items purchased from purdtl whoose vatrate=12 and
and group on same itemcode and batchno and the total sale of that item from
saledtl their can be multiple records of same itemcode and batchno the
records should be group on itemcode and batchno
Yousuf Khan
Programmer
"MC" wrote:
> Its not enough info.
> On what column you want to join? If you cant join tables directly, what is
> the relationship? You also didnt post sample data, post a couple of rows in
> both tables and then tell us what the result should be...
>
> MC
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:BB081002-5B93-4BFD-BC10-15622CDE71B2@.microsoft.com...
> >i have already told in my first question how i wanted the result and
> > i have given the fields also
> > --
> > Yousuf Khan
> > Programmer
> >
> >
> > "Uri Dimant" wrote:
> >
> >> > Can you please give me example using my tables i am not able to group
> >> > it
> >> > coz
> >> > both table are detail tables
> >>
> >> Yes , if you provide DDL+ sample data + expected result
> >>
> >>
> >>
> >>
> >> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> >> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
> >> > Can you please give me example using my tables i am not able to group
> >> > it
> >> > coz
> >> > both table are detail tables
> >> >
> >> > ]
> >> > --
> >> > Yousuf Khan
> >> > Programmer
> >> >
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Hi
> >> >> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
> >> >> purdtl.itemcode=saledtl.itemcode
> >> >>
> >> >> Take a look at GROUP BY clause in the BOL
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> >> >> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
> >> >> >I m having 2 details table as purdtl and saledtl
> >> >> > i wanted to write a query by joining both details tables
> >> >> >
> >> >> > purdtl table is having fields like itemcode,
> >> >> > batchno,qty,purno,rate,vatrate,vatamt
> >> >> >
> >> >> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
> >> >> >
> >> >> > i want to write a query having
> >> >> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
> >> >> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
> >> >> > from both the tables
> >> >> > plese help me for writing above query
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> > --
> >> >> > Yousuf Khan
> >> >> > Programmer
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

groug query

"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O5a0ykFZGHA.4836@.TK2MSFTNGP05.phx.gbl...
> Yes , if you provide DDL+ sample data + expected result
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
>|||both table are having same type of data
itemcode, batchno, qty, rate and their can be repeatation of same itemcode
and batchno
table purdtl is detail table of items purchase and table saledtl is detail
table of sale items
i want the result the total items purchased from purdtl whoose vatrate=12 an
d
and group on same itemcode and batchno and the total sale of that item from
saledtl their can be multiple records of same itemcode and batchno the
records should be group on itemcode and batchno
Yousuf Khan
Programmer
"MC" wrote:

> Its not enough info.
> On what column you want to join? If you cant join tables directly, what is
> the relationship? You also didnt post sample data, post a couple of rows i
n
> both tables and then tell us what the result should be...
>
> MC
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:BB081002-5B93-4BFD-BC10-15622CDE71B2@.microsoft.com...
>
>

Friday, March 23, 2012

Grouping

Wednesday, March 21, 2012

grouped by month

Group Update of Prices

Monday, March 12, 2012

GROUP BY with WHERE?

Friday, March 9, 2012

GROUP BY problem

Wednesday, March 7, 2012

group by issue

Group by hour, month etc

Sunday, February 26, 2012

Group By clause with an inserted column

Friday, February 24, 2012

group by

Group By

Sunday, February 19, 2012

groug query

groug query

Firehose mode

Blog Archive

About Me