• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Help with Group By clauses

I have a sql statement as follows:

SELECT USER_REGISTRATION_ID,DEALS_NAME,VENDOR_NAME,PROCESS_DATE FROM NOTIFYDEALS_LOGISTICS_VIEW GROUP BY USER_REGISTRATION_ID

I wish to get the following output.

Generate a list that is grouped by user_registeration  and get the total count (based on the no of occurrance of the user_registration-id in the table), Also i wish to retrieve the remaider of the columns.

0
TECH_NET
Asked:
TECH_NET
  • 2
1 Solution
 
TECH_NETAuthor Commented:
I first part was achieved by the following query
SELECT USER_REGISTRATION_ID,Sum(USER_REGISTRATION_ID) FROM NOTIFYDEALS_LOGISTICS_VIEW GROUP BY USER_REGISTRATION_ID
but how do i get the remainder of the column and still show the aggregrate
0
 
SQL_SERVER_DBACommented:

SELECT USER_REGISTRATION_ID,
count(USER_REGISTRATION_ID) idcnt,
DEALS_NAME,VENDOR_NAME,PROCESS_DATE FROM NOTIFYDEALS_LOGISTICS_VIEW GROUP BY USER_REGISTRATION_ID,DEALS_NAME,VENDOR_NAME,PROCESS_DATE
0
 
TECH_NETAuthor Commented:
I have 3 records and all the record show up but i do not see the count showing up with the right value. Since all the 3 records are for the same USER, (ie USER_REGISTRATION_ID =1)
the resultant should be 3 records showing the count column value (ie idcnt) as 3.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now